Preserve format in pivot table

I have a pivot table where I formated the numbers using 
the "accounting" format.  When I refresh the table, it 
goes back to the "general" format.
How do I keep the "accounting" format when I refresh?
Thanks

0
anonymous (74722)
5/5/2004 8:18:28 PM
excel.charting 18370 articles. 0 followers. Follow

2 Replies
680 Views

Similar Articles

[PageSpeed] 38

Things to try -- if they don't work, you could record a macro as you
refresh and reformat the pivot table. Then, run that when you want to
update.

Set the pivot table to preserve formatting:

--On the pivot toolbar, choose PivotTable>Table Options
--Add a check mark to 'Preserve formatting', click OK

Depending on your version of Excel, you may also have to enable
selection (From the Pivot toolbar, choose PivotTable>Select, and click
on Enable Selection)

--Instead of selecting the cells to format numbers, right-click the
field button, and choose Field Settings. Click Number, and choose your
formatting options there.

--Before formatting cells, use the selection feature to select the
cells. For example, move the pointer to the top of a column in the pivot
table (just above the column's heading cell). When the black arrow
appears (like the one that appears when the pointer is over a column
button), click to select the column in the pivot table. Then apply the
formatting.


Mike G wrote:
> I have a pivot table where I formated the numbers using 
> the "accounting" format.  When I refresh the table, it 
> goes back to the "general" format.
> How do I keep the "accounting" format when I refresh?
> Thanks
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/5/2004 8:27:59 PM
But in my pivot table it says, that you cannot mark/unmark the "Preserve
Formatting"-option!
What then?

Anders

"Debra Dalgleish" <dsd@contexturesXSPAM.com> skrev i en meddelelse
news:40994E4F.8030901@contexturesXSPAM.com...
> Things to try -- if they don't work, you could record a macro as you
> refresh and reformat the pivot table. Then, run that when you want to
> update.
>
> Set the pivot table to preserve formatting:
>
> --On the pivot toolbar, choose PivotTable>Table Options
> --Add a check mark to 'Preserve formatting', click OK
>
> Depending on your version of Excel, you may also have to enable
> selection (From the Pivot toolbar, choose PivotTable>Select, and click
> on Enable Selection)
>
> --Instead of selecting the cells to format numbers, right-click the
> field button, and choose Field Settings. Click Number, and choose your
> formatting options there.
>
> --Before formatting cells, use the selection feature to select the
> cells. For example, move the pointer to the top of a column in the pivot
> table (just above the column's heading cell). When the black arrow
> appears (like the one that appears when the pointer is over a column
> button), click to select the column in the pivot table. Then apply the
> formatting.
>
>
> Mike G wrote:
> > I have a pivot table where I formated the numbers using
> > the "accounting" format.  When I refresh the table, it
> > goes back to the "general" format.
> > How do I keep the "accounting" format when I refresh?
> > Thanks
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
6/3/2004 10:30:40 AM
Reply:

Similar Artilces:

Conditional formatting if condition of an if statement is true and a string value is displayed
Dear Experts: I got a nested 'IF'-Formula in an excel cell that returns nothing if the condition is false. I would like to conditionally format this cell with grey shading if the condition is true and a string value is displayed. How can this be achieved using excel conditional formating functionality ? Help is much appreciated. Thank you very much in advance. Regards, Andreas Let's say your doing this conditional formatting in cell K17. In conditional formatting (xl2003) choose the FormulaIs: option and enter: =$K$17<>"" choose your formatt...

Pivot Tables
Hi, I am having trouble creating a pivot table to show variance between planned vs actual units. Currently my data is broken down into the following fields, Team, Type, Month, Actual Units, Planned Units. I would like the pivot table to show if the current teams are meeting their monthly goals by comparing the planned vs actual units and showing the percentage. Team (all) (page) Month Jan Feb March . . . . . (column) Actual 100 110 90 Planned 90 110 100 Variance 10 0 -10 variance % 11% ...

Pivot Table
I've a column in a pivottable as 'running total'. I want to add another column 'percent' (not from the data in the source) which will give me percentage of the 'running total' column. That is, for instance: running total percentage 12 34.3% 24 68.6% 35 100.0% Thanks. http://www.contextures.com/xlPivot10.html there are instructions for running total -- Regards, Peo Sjoblom (No private emails please) "Pivot Table" <...

Two tables, one form
I have a form that I created in AC2003. This form will merge info from two tables. So I assigned Table1 as the source for the form, inserted all of the columns from Table 1. Next, I changed to source for the form to Table2 and again inserted all of the columns from Table2 onto the form. I then changed the name of all Table2 columns to precede it with an "n". i.e. FirstName became nFirstName. All source for those items for Table2 were changed to have no source. Again, the source for the form is Table1. I lookup a row in Table1 in Table2 and if a match is found, ...

Excel formatting issues
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Recently I have started having problems with excel not displaying the correct format. For instance I format the cell for currency and the number doesn't change to currency. Also, don't remember having the zero value problem before-numbers starting with zero not showing the Zero. It almost like I have some kind of bug or something. I have the latest update v 12.2.3 any suggestions? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may no...

Data Validation Query:
Hi Guys, First Post. I need help with the following: I have a row of cells that is custom formatted as H:MM 1) Users enter their overtime in hours and minutes (H:MM) What I am trying to do is have data validation that they can only put their overtime H:MM 2) I try and validate their input by going to DATA | VALIDATION | ALLOW | CUSTOM | and custom = H:MM For some reason this will not work. Does anybody know how to get this working? Any Help would be much appreciated. Rgds, Bw --- Message posted from http://www.ExcelForum.com/ You can choose "Time" from the Allow list...

Having Formatting Remain on "Called" cell using IF function
I'm using an IF function to call some other cells, but I want to have the various words color coded, eg ("Certified" is green, "Recertify" is yellow, and "Not Certified" is red). I have the IF function working correctly, but when it "calls" the cell to have it display the appropriate word, the color of each word is not brought along with it...only the text. Is there a way to be able to color code it so that the appropriate color appears? Thanks! -- Zaraf ------------------------------------------------------------------------ Zaraf's Profi...

Can't modify table style
I'm flabbergasted after spending over 30 minutes trying to trouble shoot a table of contents, so I deleted it and tried to create a new table of contents formatted like the one I had before (font properties wise). So I select the table of contents tab on the ribbon (Word 2007), then I select "Insert a tabel of contents..." under the automatic and manual table images above and I want to "modify" the "formal" format, since it's pretty close to what I want except for the font color, type and size. I also want to change the "options&quo...

Pivot Tables, refreshing with additional data
This is probably obvious, but I can't figure it out or find it anywhere. I have a pivot table. At its simplest, it has "Ink Types" in rows, "Date" in Columns and Sum of "QTY" as data. I have grouped the Date fields by Month and Year This pivot table is based on a table on another sheet that contains specific sales data, with the date in mm/dd/yy format. If I select the specific rows in the data table that contain data, I can create my pivot table and everything is hunky-dory. However, I want to keep adding to this table, as new sales are posted. S...

parsing HTML table into excel. How?
Hi there, I do have (many) links with HTML text formated in tables. I would like to convert those links (pages) regularily to an excel file just containing parts of the original HTMP table. What is the best way to achieve this? Cut and paste is out of the question. There are too many links. Any pointer is welcome. Thanks a lot Dan Dan, when you say "page", do you mean worksheet? Or are you talking sections of a worksheet as a page? Bob Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel...

Using Pivot Table with VLookup
Folks, I have two sets of Data sitting in two separate worksheets. For the purposes of my reconciliation work, I need to compare the sets with one another. For example, Set 1 has the following 3 fields(and accompanying information): Name Ticket # Fare Johnson/F 1692742117 2075.23 Johnson/F 1692742117 (2,075.23) PMT/ON 5027246983 67,851.36 PMT/ON 5027246983 (67,851.36) Singh/F 1698798564 3250.36 Mirer/G 9707266335 542.36 Mirer/G 9707266335 (542.36) Set 2 has the following information: Name Ticket # ...

What is the best way to create a conversion table in Excel ?
I want to create a conversion table where at the prompt of a given measured value, Excel will return the value in a (host of) new measurement(s). Example: If for gas, i want to convert 1 cubic meter into kcal, I would enter 1, choose cubic meter as my initial measurement, and then easily read off a table what this equals in kcal. see this url interestsing martin a. <martin a.@discussions.microsoft.com> wrote in message news:693B27D8-E553-4699-8295-63450807D1A7@microsoft.com... > I want to create a conversion table where at the prompt of a given measured > value, Excel will re...

2007 Charts not retaining formatting
We have a spreadsheet that we have put together with a number of calculations and charts. Several scheets have two charts on it with the data and some comments. The system keeps changing some of the chart formatting such as label sizes, color or even the size of the whole chart. This can happen when we get ready to print several sheets or even save the file exit Excel and reopen the file. It seems to have a lot of loose ends with things just happening to the formats in the file. This was a new spreadsheet created as a 2007 Macro Spreadsheet. (We had to use a custom function since ...

Create Table Record on Current of Form
I have a table called LastVisitedRecord. There is but one field in that table called "lvCompanyID". I'd like to use the OnCurrent event of my record form to insert the record id (CompanyID) into a new record in the LastVisitedRecord table. After that, I'd like to use a query I've created qLastVisitedRecord which only shows the top 1 record of the LastVisitedRecord table to query my contact form by default, showing all records, but skipping to the record whose CompanyID field matches the sole record in the query qLastVisitedRecord. Anybody got an idea on how to do this? ...

Update table with a Running Total
Hi, I have a table [Table1] with the fields laid out like this... StationID Date Value CumulativeValue What I need to do through VBA is populate the YTD [CumulativeValue] for each [StationID] for each [Date]. The dates for each Station run from Apr 1 to Oct 1 and the [Value] field contains an Integer value for each day. So what I'm really wanting to do is populate the YTD [Value] for each [StationID] for each [Date]. Thanks, Ken Instead of storing that CumulativeValue in the Table, you can see that thru a Query Report where you havs "From - To" dates. HTH - Bob "KP...

message tracking log format
We are about ready to upgrade from E2k to E2k03. Yes, we know E2k07 is out but we aren't ready to go there yet :) We use the message tracking log to generate some internal security reports. We've noticed a change in some of the data. In Exchange 2000, the log showed the sender or recipient in standard smtp format user@domain In Exchange 2003, it appears that sometimes it shows in an LDAP DN format. So my questions are: 1) is that a configurable option? 2) if not, can someone explain, or point to a KB article, that explains the entries? What is confusing us is the "sometim...

Cell Format #7
Hello, I have a column where I would like to format as ##-##-##- ##-##-##. I went to Format > Cells and format it in Custom category. It works fine when I enter values, but when the data starts with a text, it won't work. Any suggestions. When I entered 123456789123, it converts to 12-34-56-78- 91-23. When I entered AU1234567890, it doesn't do anything. I want it to covert to AU-12-34-56-78-90. Any ideas would be helpful. You can only format numbers that way, you could custom format as "AU"-##-##-##-##-## and just enter the numbers 1234567890 -- Regards, ...

How can I sum amounts formated as B O L D only?
Hi! I need to get a total from a worksheet that has hundreds of amounts in it. However, I only need the total of the amounts that were marked with have BOLD font. Please help me I don't have much experience with EXCEL. Thank you very much! W a n d a Hi, Wanda- By any chance, is there a word next to the bold number? Maybe the word "Total"? It would be easier to derive the total using a word than using the bold format. On Fri, 11 Mar 2005 11:37:06 -0800, "wanda" <wanda@discussions.microsoft.com> wrote: >Hi! >I need to get a total from a worksheet...

limiting data in a pivot table
I have a pivot table that is sorted by location, then machine, then has a list of downtime which is totaled by machine. What I want to do is only display the machine that are over a certain amount. I cant use the options to display the top so many because that constantly changes. ...

Function that meets table requirement then multiplies by table fac
I'm looking for a function that will reference a cell then lookup that cell in a table and use the multiplier in the table. I'm not explaining very well so Im hoping that my example will help you understand: Column A b c Sector P&L w/o Admin P&L w/ Admin PC 517.9 PC 0.0 PC 0.0 TD 1,993.6 TD (2,508.0) TS 431.3 TS 355.7 Table reference: FD 1.20 PC 1.03 PR 1.52 SIJ 1.00 TD 1.32 TS 1.63 In column C I would like to put a formula that; if column A mactches column A in table reference th...

XSLT number-format command and scientific notation (HELP!)
Well, the project I am working on has now come to a screeching halt! I have been developing a program that heavily utilizes ADO.NET record sets. To generate reports, I convert the recordset to XML, and then apply an XSLT to transform the XML into HTML. This works great (or did) until today. I just found out that the "number-format" command in XSLT can't handle scientific notation! So when I try to format these numbers I just get "NaN" on the output report!!! There are only two solutions I can see, and don't know how to implement either of them. One would be to get ...

Sorting Table of Contents
I have only just starting using Word 2007 and I am having trouble with my Table of Contents My document is finished and runs to 32 pages My Headings are marked and I only use "Heading 1" and page number is right aligned In Word 2003 I could make it so that every time the TOC was updated it would sort the Table Alphabetically by the Name in the the heading This one just updates the table as it finds it - very hard to find anything Is there any way to makle it automatically sort A-Z everytime it is updated Hi Nylex, TOCs always sort by relative position in the docume...

Query from three tables, problem when adding a record
I'm using Access 2002/2003. I have three tables, A, B and C. A has a primary key, which is linked to corresponding foreign keys in B and C. In each table the key values are unique. I have a query Q showing all rows from A linked to the corresponding rows from B and C. Calling Q, I can add a new row to A. I can also add a new row to B and a new row to C at the same time. But in case I add a new row only to A and then close the query, opening the query again does not allow me to add the corresponding new rows to B and C. Acces refuses to store these new rows. Can I modify the query to...

Underline % format
How can I get cells that have the % format to have underlines that are similar to what you get with the accounting format? Will a custom format do it? Will Fleenor Will@k2e.com Just format the cells with the "percent format" of your choice, then highlight the cell(s) again and right-click > FormatCells > Font tab > select single accounting underline........... Vaya con Dios, Chuck, CABgx3 "Will Fleenor" <will@k2e.com> w7yuuuuuuuuuuu][[[[[56tttttttttttttttttttt[[[[[[[[3222rote in message news:eKr3Y5IWFHA.2960@TK2MSFTNGP15.phx.gbl... > How can I g...

Tables and Borders
I am using Office 2000. When I do "View Toolbars" under Word...one of the options is Tables and Borders. Under Excel, this option is not available. Is it possible to add this option so that I can customize my spreadsheet ....border width, eraser...etc. Thanks Sakman Hi, The feature you want, called the Borders toolbar, was added in Excel 2002. It's not available in 2000. 2010 is comming out sometime in the not too distant future, might we about time to upgrade. That software is a decade old. -- If this helps, please click the Yes button. Cheers, Shane Devenshire &...