Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
In my pivot table, whenever I select a new item through the drop down box of
my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give -- Thank You Andrew Edmunds |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
As far as I know, you can't avoid it. Record a macro when formatting,
and play it after each update. Regards. Daniel In my pivot table, whenever I select a new item through the drop down box of my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
You have to apply your formatting to the fields in the pivot table rather
than the rows & columns in the worksheet. If you format a whole column that includes part of your pivot table, for example, the formatting will be lost when the pivot table is refreshed. To format a field in the pivot table, put the cursor right over the heading of the desired field, then slowly move the cursor upwards. Left-click when it changes to a downward-pointing arrow. All the data for that field will be selected. Now select Format Cells select your desired formatting OK. Formatting applied this way will persist when the pivot table is refreshed (such as when a new item is selected). Hope this helps, Hutch "AndrewEdmunds" wrote: In my pivot table, whenever I select a new item through the drop down box of my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give -- Thank You Andrew Edmunds |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
Hi Tom;
I think that Hutch has part of the answer. However, it all depends in what Excel you are on. So it is this simple: Office 2003: 1- Righ click your mouse any where in the pivot table. 2- Choose the option, Select Entire Table (You will see all highlighted) 3- Right click again and this time click on Select Data (Only the data section should be highlighted) 4- Right cliclk again and then click on Format cells and choose the format you wish to show the data One of the issues of the 2003 is that it will not keep the other formats such as the width and the height of the rows and columns; however, the format of the values would be kept the the Pivot Table. Office 2007: Office 2007 makes it easier; however, I am not happy about other things. 1- Define the pivot Table 2- By hand, highligh the area in the PV that you wish to control the format 3- Go straight to the Cells format option using the Menu, or the right click of the mouse and go ahead and change the format. This should do it. In the 2007 yo can even have different formats for each cell of the PV if you wish. In other words, the control of the cells format is done outside of the PV. I guess that this is possible since Microsoft moved to used now the XML format. Good Luck and whether you have any issues, reply and I would try to see what happen. -- Lost again "Tom Hutchins" wrote: You have to apply your formatting to the fields in the pivot table rather than the rows & columns in the worksheet. If you format a whole column that includes part of your pivot table, for example, the formatting will be lost when the pivot table is refreshed. To format a field in the pivot table, put the cursor right over the heading of the desired field, then slowly move the cursor upwards. Left-click when it changes to a downward-pointing arrow. All the data for that field will be selected. Now select Format Cells select your desired formatting OK. Formatting applied this way will persist when the pivot table is refreshed (such as when a new item is selected). Hope this helps, Hutch "AndrewEdmunds" wrote: In my pivot table, whenever I select a new item through the drop down box of my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give -- Thank You Andrew Edmunds |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
I really appreciate all of the comments and help with this problem. I tried
Tom's way where I waited for the down arrow and then reformatted it to justify to the left and bottom and it still went back to center and center when I changed the item. I also tried right clicking and selecting entire sheet, and then right clicking and selecting labels (as it is the labels I am struggling with keeping format). After doing that when I switched items the format reverted back to center. I ended up writing a macro to format after I swich items. I really do appreciate the help and please feel free to let me know if you think I didn't follow your advice correcly. Thanks again -- Thank You Andrew Edmunds "Argy" wrote: Hi Tom; I think that Hutch has part of the answer. However, it all depends in what Excel you are on. So it is this simple: Office 2003: 1- Righ click your mouse any where in the pivot table. 2- Choose the option, Select Entire Table (You will see all highlighted) 3- Right click again and this time click on Select Data (Only the data section should be highlighted) 4- Right cliclk again and then click on Format cells and choose the format you wish to show the data One of the issues of the 2003 is that it will not keep the other formats such as the width and the height of the rows and columns; however, the format of the values would be kept the the Pivot Table. Office 2007: Office 2007 makes it easier; however, I am not happy about other things. 1- Define the pivot Table 2- By hand, highligh the area in the PV that you wish to control the format 3- Go straight to the Cells format option using the Menu, or the right click of the mouse and go ahead and change the format. This should do it. In the 2007 yo can even have different formats for each cell of the PV if you wish. In other words, the control of the cells format is done outside of the PV. I guess that this is possible since Microsoft moved to used now the XML format. Good Luck and whether you have any issues, reply and I would try to see what happen. -- Lost again "Tom Hutchins" wrote: You have to apply your formatting to the fields in the pivot table rather than the rows & columns in the worksheet. If you format a whole column that includes part of your pivot table, for example, the formatting will be lost when the pivot table is refreshed. To format a field in the pivot table, put the cursor right over the heading of the desired field, then slowly move the cursor upwards. Left-click when it changes to a downward-pointing arrow. All the data for that field will be selected. Now select Format Cells select your desired formatting OK. Formatting applied this way will persist when the pivot table is refreshed (such as when a new item is selected). Hope this helps, Hutch "AndrewEdmunds" wrote: In my pivot table, whenever I select a new item through the drop down box of my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give -- Thank You Andrew Edmunds |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I keep my pivot tables format from changing?
I think I just found the problem. All of the advice I received was correct,
the reason it didn't work for me at the time was because I had merge labels check in the pivot table options. Once again thanks for all of the help -- Thank You Andrew Edmunds "AndrewEdmunds" wrote: In my pivot table, whenever I select a new item through the drop down box of my field my formatting gets screwed up. I have unchecked auto format table and I have checked preserve formatting. For example, I have a balance sheet and I use the drop down box to select different facilities in order to see their balance sheet values. Whenever I select a different facility, all of the row headings go from being left justified to centered and bottom justified to centered. I appreciate any advice you could give -- Thank You Andrew Edmunds |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Data Source on Multiple Pivot Tables at once | Excel Worksheet Functions | |||
pivot tables changing their formats when data is refreshed | Excel Discussion (Misc queries) | |||
Dynamically changing print areas for Pivot Tables | Excel Discussion (Misc queries) | |||
Pivot Tables -changing datasource for exsting Pivot Table | Setting up and Configuration of Excel | |||
Dynamically changing several pivot tables at once | Excel Discussion (Misc queries) |