How to make formatting 'stick' in Excel pivot tables

Kashflow logo
Share this content

The normal way of formatting cells in Excel doesn't work with pivot tables. To make the formatting 'stick' you have to use the formatting options from the special pivot table menus. David Carter explains how.

The normal way of formatting cells in Excel (highlight the area, then Format - Cells at the top of the screen) doesn't work for pivot tables. Or rather, it will work once, but as soon as you Refresh the data the formatting is likely to disappear.

To make the formatting 'stick' you need to highlight the cells via the pivot table's own menus. These are a bit odd in some ways, and differ between versions. But these notes should help you work it out.

To format the numbers in the data area

Create the pivot table, then right click anywhere on the numbers to bring up the pivot ta...

Please Login or Register to read the full article

The full article is available to registered members only. To read the rest of this article you’ll need to login or register. Registration is FREE and allows you to view all content, ask questions, comment and much more.

About AccountingWEB


Please login or register to join the discussion.

19th May 2004 15:15

Having got rid of "Sum of"
Just as a side comment to David's note I do the same as him except put a space after all the words too. That way if you centre align the words they truly centre align and arent just off to one side.

Thanks (0)
15th Mar 2004 21:51

You can get rid of "Sum of", you know
A small point, Excel automatically generates "Sum of" or "Count of" in the column heading.

Often this makes the column heading too wide. If you want to get rid of Sum of, just right click on the data to get the pivot table menu, then select Field Settings.

This brings up the Pivot Table field box containing Sum, Count, Average etc. At the top of the box "Sum of Amount" or whatever is highlighted, so you can change it.

Personally, I always click just to the right of the "f" in Sum of, then delete everything to the left. The reason for this is that if you make the field just contain "Amount" Excel gets upset because it is the same as the field name. So make it blank space Amount instead.

Thanks (0)
09th Mar 2004 11:00

Column Widths
I tried Philips method, only to find that on refreshing the widths reverted bach to their previous setting.

A crude alternative I found often works to increase column width is simply to add the required no. of blank spaces to the end of the column heading. Not very scientific but effective.

Thanks (0)
09th Mar 2004 20:35

Why didn't I think of that?
Paul - excellent idea. It might not be very scientific but who cares, it must work. Just make sure all column headings are the same number of characters and allow for "Sum of" to be added.

Then of course reformat the labels as wrapped text fields. And you'll probably be back where you started!

Thanks (0)
02nd Feb 2004 13:49

Bordering on suicide...

I see how this all works - except it doesn't work with borders - they're just cleared when I refresh! I'm using Excel 2002 - do you think this is a bug?

'Irritated of Aylesbury'

Thanks (0)
03rd Feb 2004 11:32

You're right!
Julian. yes, I'm losing the borders on my version of Excel too.

Looks like a bug. We'll pass this one on to Microsoft Support and come back to you with their comments.


Thanks (0)
05th Feb 2004 19:26

Column Widths - I know what you mean!
Yes, I've had the same problem with the column widths. The best solution I've found is to use Enable Selection, highlight all the columns of the data area and then select 'Labels only' (I'm referring to Excel 97). Now, format the labels so that Number is set to Text and Alignment is set to Wrap Text. Then set the columns to the widths you want. And save the table!
It doesn't always work but usually it does and it seems more 'robust' when all columns are the same width - but that might just be my imagination.
PS - if you come up with a better way please let me know!

Thanks (0)
05th Feb 2004 16:32

What about column widths
How can I get the column widths within a pivot table to auto-fit or retain a size I set?

When I refresh the columns go back to their original size.


Thanks (0)
23rd Apr 2007 17:30

Making column widths 'stick' in pivot tables
I've found that I can make all of my formatting preferences 'stick' by unchecking the 'Autoformat table' option within the 'Table Options' menu.

To clarify, right click, anywhere within the pivot table. Select 'Table Options'. Uncheck 'Autoformat table' (3rd option in the list). And ensure the 'Preserve formatting' option is checked (6th option in the list).

You're done.

Thanks (0)
08th May 2007 10:03

what version are you on?
Hi Geoffrey It looks like MS have been gradually clearing up the formatting over the years Are you on 2003? Back then I guess most of us were on Excel 2000 or 97.

Thanks (0)

Related content