Pivot Tables – saving formatting changes?? (Excel 2003)

Topic

New Reply

I have a Pivot Table set up from a large data source. None of the “Automatic Formats” of the Pivot Table are what I need. For example, I want to have most of the columns centered, and on the Auto Formats they’re usually right- or left-justified; I want my “date” columns to be in M-DD-YY format, but all of the Auto Formats seem to default to MM-DD-YYYY; also I have some columns that I want to “wrap text” and Autofit the Row Heights so everything in those cells is visible, and the defaults do not do this. So I’ll manually change the appearance so it looks the way I want it. The “Preserve Formatting” under table options is clicked. But whenever I refresh the data, or choose a different Page Data drop-down option, it changes everything back the the Auto Format defaults again. ARGH!!! . What is that “Preserve Formatting” there for, anyway? What “formatting” DOES it preserve?

How can I format the table to look the way I want it to, so it KEEPS it that way whenever I refresh data, etc.? Is there a way?

Reply | Quote

Replies

I have found that you should use the format arrows to select the data to format. This preserves the formatting when things are updated and moved. If you select cells in the pivot table it does not preserve the formats. Move to the edge of the field, and when your mouse pointer changes to a black arrow, click to select the info.

Reply | Quote

I’m having the same problem – formats keep changing back to some default. But I don’t know what format arrows are. Where do you find them? I don’t have any info to select. Do you select the whole pivot table? Is the edge of the field, the edge of the pivot table?

Thanks.

Reply | Quote

See the attachment.
If you move your mouse the the areas in red, you can select those areas of the pivot table, add formatting, and this will then be preserved in the Pivot Table.

Reply | Quote

Wow… I’ve been wondering this same thing for years… sometimes my formatting stayed, and sometimes it didn’t! Guess I was doing it right half of the time and didn’t know it. Great info! Thank you! I am wondering though if it’s possible to get rid of ALL the line formats in the pivot table. When I do as you suggested, it works for all the lines except the lines that look like I have the “freeze panels” on, which of course I don’t… it’s the one horizontal line and one vertical line that separate the field headings from the data? Is this possible?
Thanks!!
LJM

Reply | Quote

Try the following:
– Click in any cell of the pivot table.
– Select Format | AutoFormat… (the entire pivot table should be selected automatically).
– Select the very last option (labeled None).
– Click OK.
You should now have a pivot table without any lines, and this should persist when the pivot table is refreshed.

Reply | Quote

Perfect! You rock Hans… thanks so much!!
LJM

Reply | Quote

See the attachment.
If you move your mouse the the areas in red, you can select those areas of the pivot table, add formatting, and this will then be preserved in the Pivot Table.

Reply | Quote

I’m having the same problem – formats keep changing back to some default. But I don’t know what format arrows are. Where do you find them? I don’t have any info to select. Do you select the whole pivot table? Is the edge of the field, the edge of the pivot table?

Thanks.

Reply | Quote