I am having trouble keeping numbers formatted in a Pivot Table

I have a pivot table that won't stay formatted. The data is formatted as 
currency in the worksheet that the Pivot Table is drawing off of. It shows up 
as a general number in the Pivot. I've tried formatting it in the Pivot but 
it doesn't stay when I refresh the data. "Preserve formatting" is checked on 
the Pivot table, so it SHOULD be preserving any formatting I apply to the 
pivot table, but that doesn't seem to be happening. I know I could record a 
macro to format this, but it is happening on multiple Pivot tables in the 
same workbook and that would be a pain to run the macros every time I refresh 
the data. I'd like to just resolve the problem. Any suggestions? 
0
H (152)
4/22/2005 4:04:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
570 Views

Similar Articles

[PageSpeed] 18

You may have to enable selection (From the Pivot toolbar, choose
PivotTable>Select, and click on Enable Selection)

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.


Liana H wrote:
> I have a pivot table that won't stay formatted. The data is formatted as 
> currency in the worksheet that the Pivot Table is drawing off of. It shows up 
> as a general number in the Pivot. I've tried formatting it in the Pivot but 
> it doesn't stay when I refresh the data. "Preserve formatting" is checked on 
> the Pivot table, so it SHOULD be preserving any formatting I apply to the 
> pivot table, but that doesn't seem to be happening. I know I could record a 
> macro to format this, but it is happening on multiple Pivot tables in the 
> same workbook and that would be a pain to run the macros every time I refresh 
> the data. I'd like to just resolve the problem. Any suggestions? 


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

0
dsd1 (5911)
4/22/2005 6:43:01 PM
Reply:

Similar Artilces:

Long numbers show up as Scientific Notation
Hello, We have a web based asset tracking system, and some assets have long serial numbers; for instance, 3100600300030147987 or 3101600300100020220 When we import, cut and paste, or open an HTML format page in Excel with these serial numbers, they all show up as: 3.1016E+18 and the last 6 digits get rounded up. How do we show the correct value for these long numbers in excel? Thanks On Mon, 7 Feb 2005 08:59:07 -0800, berryware421243 <berryware421243@discussions.microsoft.com> wrote: >Hello, > >We have a web based asset tracking system, and some assets have long seria...

How to save spreadshhet in ASCII format?
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C48706.E76906F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am trying to save an Excel spreadshet in a format (I think it would be = ASCII) suitable for importing to another software package. The file = widths (fields) need to be exact. For some reason I am unable to find a = format in Excel that will work. I am using MS Office Pro 2002. =20 Thanks in advance for your assistance. WF ------=_NextPart_000_000E_01C48706.E76906F0 Content-Type: text/html; cha...

How to diffrentiate Text & number from a single cell????
Hi ther guyz............i a have small problem with the execl sheets........... my problem is e.g. i have alot of cells with a number and some text in it........and they both are in one/single cell (e.g. "878 queen st" in A1)....now what i want is to remove let's say "queen st" from each cell so that there is only number left in it.....................i cannot figure out any formula........if there is any..............need help imediately -- pack_card2000 ------------------------------------------------------------------------ pack_card2000's Profile: http://www...

Three different fields in one record referencing to three different records in another table
Hi. I have TableA and TableB TableA has 3 fields where types of file extensions (for different purpose) are supposed to be stored. Table B has many extension type strings for these TableA three fields (.tif, .jpg, .pdf, .gif, etc). 3 combo boxes refer to Table B to get and store TableB keys in TableA appropriated fields. When I set up table retaionship, Acces 'creates' also TableB_1 and TableB_2 and show a diagram where two other similar to tabelB tables show up. So long so good. I can create records in TableA having chosen, for each different file purpose, the respective extens...

Data format for pages
I want to change the format control properties "DataFormatAs" to a currency in Data Access Page (access 2000). Its asking for a code but I dont how?. Its easier in newer versions Select the Data Access Page > choose design > double click the field/cell whose format you want changed > Hit Design tab > Format > Change to Currency. -- HTP Adnan "Newguy" wrote: > I want to change the format control properties "DataFormatAs" to a currency > in Data Access Page (access 2000). Its asking for a code but I dont how?. Its > easier in newer ...

Time Formating
I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as 12:52:11. For a possible solution see http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom "Denise" <anonymous@discussions.microsoft.com> wrote in message news:4804E8F2-C978-48DC-B163-67029B218F7F@microsoft.com... > I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as ...

Visio Text (Number) Automation
Hi. I'm creating an Organization Chart for my company. I have the org charts already setup with names, shapes, etc; however, I would like to add the total number or employees in each group, which would add up to larger group, and finally all the way to the top which will have the total number for all employees. i would like to somehow automate this process like excel does wtih a formula (=techdept+accoutningdept) which would add the numbers i manually filled in the tech and accouting dept, but would add those number together for the group above that. Thanks. ...

IIS Admin Service keep failing
Hi there, We have been running Exchange2003 on Win2003 Standard for the last year with little or no problems. Since December though, IIS Admin service keeps failing along with FTP Publishing, Smtp and Exchange Routing Engine. It auto restarts the services until the count reaches 50 and then stops (it reaches 50 in about 24 hours) We are running the latest levels on Server2003 and Exchange2003, EX Best Practices Analyser comes back fine. I found 1 article about this but it was to do with 2000 and says I dont need to apply the patch as it is included in SP1 System Log gives event ID 703...

ROUNDED NUMBERS IN FORMULA'S
Can anybody help me to set up Excel so that it uses rounded numbers in formulas? I have got the cell formatted to 'show' the number rounded to two decimal places but the complete root number (shown in the formula bar), is used! Eg. 1.5642 (1.56 shown) x 4 = 6.2568 (6.25 shown) I want to multiply 1.56 x 4 = 6.24, without going into each individual cell and removing the unrequired numbers. I hope that somebody can help me - even if it is to tell me to stop trying because it's maybe not possible. I thank anybody in advance for any constructive response. bing1080 =round(A1...

keep getting error message when importing clipart
While in Publisher I am trying to import clipart and keep getting this error message: The appropriate graphic converter is not available. I have reinstaleld my software using the full install setting. I have downloaded all updates and it still won't work. Can somone help me? Clear your cache, in IE, tools, Internet Options, delete files. If that doesn't cure your issue, post back. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Karon" <Karon@discussions.microsoft.com> wrote in message news:6A2A64FD-B544-4...

Number of rows in a worksheet
I have a very large data file. It has 110,000 records approximately. want to import it into an excel worksheet. I did so using the Impor Text function and defining the field widths with !. Now the most I can seem to import onto a worrksheet is approximatel 65,500 records. The wizard then advises me to import the rest ont another data sheet, excluding the data already imported on to the firs sheet. When I try to do this, the wizard will only allow me to exclude th first 32,000 records from the second import and then will only impor approxiately 32,000 more records. In effect the second impor...

conditional formatting
I have a conditional format which the formula indicates true for column "A" and column "B" background shades red. how do I set up so columns "A-T" highlight red and set-up so all rows 1-400 are formatted when column "A" is true? I have tried and cannot get it to work...thanks Joe First select A1:T400, then use CF of =$A1 Bob Umlas Excel MVP "joe@malvern" wrote: > I have a conditional format which the formula indicates true for column "A" > and column "B" background shades red. how do I set up so...

how to keep the selection colored?
I have 3 list controls in my Dialog based application. I use mouse to select 1 item in each list control. When I click 1 item on one of the list controls, it is colored "blue", but when I click another item on another list control. The first item color is not blue. How to keep it colored? Of course, if I click other item in same list control, I do not need to keep the color. "kathy" <yqin_99@yahoo.com> wrote in message news:1139254470.953258.115110@o13g2000cwo.googlegroups.com... >I have 3 list controls in my Dialog based application. I use mouse to > select...

Purchasing Mix & Match Tables- what do you think???
Microsoft should add a Mix & Match table for purchasing. It would act the same way it would for the sale of an item only it would help calculate the Purchase Orders. Example: I buy cases of coke. I need 100 cases to reach a qty discount. A purchase order report would calculate the products needed to reach the level. This would be an priority trigger over the ROP. For example if I sold 25 cases of each (coke, diet coke, caf free coke, caf free diet coke) and my reorder point was 30 case sales(for each) a purchase order would not be generated. If the Mix & Match PO could do a: cal...

trouble sending large files
I am having trouble in outlook express 6 sending large files. When I say large I mean a half meg or larger. The send bar will go to about 99% and then just stay idle at that point. Eventually I get an error message that says it has stopped responding and asks what I would like to do. Continue trying or stop. No matter which I pick it stays the same. Then I find out it sends multiple copies for each time I try to stop it. This can be very annoying to my friends who have a dial-up. Any suggestions? Are your using a dialup or broadband (cable/dsl) account? If broadband, are you behind a...

Script for changing users date format
Hi, Is there an easy way to change a bunch of users date format in one go? Thanks, Sophie Hi Sophie, Have you look into using workflow to update the data format field fo the users? Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Dec 16, 6:27=A0pm, Sophie <Sop...@discussions.microsoft.com> wrote: > Hi, > > Is there an easy way to change a bunch of users date format in one go? > > Thanks, > Sophie Hi Darren, Thanks for getting back to me. I can't find anywhere in the workflow an option to change the date format? Thanks, Sophie &qu...

inetinfo keeps crashing on backend server
"Faulting application inetinfo.exe" This application crashes about every minute and a half. the last app to start before the crash is OMA Categorizer, then in about a minute the inetinfo.exe crashes Any Ideas? Thanks Hi, You may want to look at this: KB827214 http://support.microsoft.com/default.aspx?scid=kb;[LN];827214 -- Martin Blackstone MVP - Exchange http://www.swinc.com/resource/exchange.htm http://www.swinc.com/resource/e2kfaq_appxc.htm "Gonzosez" <tbyam@hinklemfgNS.com> wrote in message news:ed04vXnmEHA.1800@TK2MSFTNGP15.phx.gbl... > "Faulti...

Formatting within this macro
I am new to VBA and can follow along fairly well, but don't know how to do certain things. I have the following macro that runs like a champ, but now I need to automatically format the SummarySheet. Specifically, I need to set Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in Office 2010 it's White, Background 1, Darker 25%). I also need to set Rows 1 and 3 to a height of 6 and have a color of Light Gray. Then I need to change the output font to Tahoma, 12, Bold. Finally, I have to have a cell at the bottom of Column F that says "Totals&q...

Link Two Tables with AutoNumber fields to One Form
Hi, I'm really stumped on this one: Right now I have a form that is linked to my Registration table. I want it to also be linked to my ID table, because I want to be able to update both tables using one form. I heard of creating a sub-form. Would this solve the trick? Right now, when I create a new record in the form, the AutoID of the Registration table increases sequentially. I want to be able to also have the ID table increase its AutoID and create a new record in that table too. That way I can use that new ID table value to create a value that will eventually be stored in...

MS Office 2008 keeps launching setup assistant
I am caught in the endless loop of trying to open an Office application only to have it quit and launch the office setup assistant, click through to opt in/out of feedback, then auto-update launches, doesn't find any updates, and then I'm back to the desktop. I have this.... http://blog.entourage.mvps.org/2008/06/1211_updater_woes.html and this... http://blog.entourage.mvps.org/2008/05/office_2008_sp1_install_causes_setup_assistant_to_loop.html and things here... http://forums.macnn.com/82/applications/363331/help-office-2008-app-launch-runs/ Nothing has worked. I cannot use of...

File format is invalid
I have a file that I update and save daily. yesterday when I went t open it I got the following message: File Format Not Valid. This pop up in middle of the screen. the only option it gives me is a butto that says OK. I have tried to recover an old version of the file usin the windows recovery tool but I keep this file on an external driv which it does not recover. Any help with how to get this file bac would be greatly appreciated. Pau -- pserra ----------------------------------------------------------------------- pserra2's Profile: http://www.excelforum.com/member.php?action=ge...

Can you apply split colour formatting to the same cell?
Hi, I'm looking to apply more than one colour format to the same cell, split diagonally from right to left (one colour being orange, the other being white/uncoloured). I don't want stripes, just two blocks of colour making up the cell background. Slightly bizarre question, I know, but it's for a work diary for a department with colour representing 'busy' and white/uncoloured representing 'available'. As I'm busy for the first half of the day, I want to split the cell in half. At the moment I just have a diagonal line and the whole cell in orange, but wan...

Formatting "Caps" in a Style
Using Word 2007 Is it possible to include the changing of font case to Capitalize Each Word in a paragraph style? I would like the text to appear as: This Text Is In 'Capitalize Each Word' Case I would also like to avoid the use of Shift+F3, if I can. TIA David Word only has "paragraph" and "character" styles. There is no "word" style, which is what you would need to achieve what you are after. The following code will capitalize each word of the selected text: Selection.Range.Case = wdTitleWord -- Hope this helps, Doug R...

conditional formatting #53
I only just discovered the possibility of conditional formatting. Apparently, only three formatting rules can be set. What is the way around this limitation if I want to make it more complex. Some formula I guess? Thanks. you must use Excel 2007 :) no limitation Conditional Formatting or may be write a macro code Ozcan ELMALI "jim3975" wrote: > > I only just discovered the possibility of conditional formatting. > Apparently, only three formatting rules can be set. What is the way around > this limitation if I want to make it more complex. Some formula I gues...

Conditional Formatting #22
I am trying to create a conditional formula that highlight every number that is less than 0 in red. I have five columns and over 200 rows with numbers in it. How would I do this? Thank you. - Michelle Select all the cells you want. Then, in Conditional Formatting, select "Cell value is", "Less than", then type 0. Then click "Format" and change the font to red. (You can also do this using the Format Cells sub-menu, under Format) Michelle wrote: > I am trying to create a conditional formula that highlight > every number that is less than 0 in red. I ...