How to make formatting 'stick' in Excel pivot tables

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.

Continued...

» Register now

The full article is available to registered AccountingWEB 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.

Comments

Having got rid of "Sum of"

AnonymousUser | | Permalink

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.

You can get rid of "Sum of", you know

David Carter | | Permalink

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.

Column Widths

AnonymousUser | | Permalink

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.

Why didn't I think of that?

AnonymousUser | | Permalink

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!

Bordering on suicide...

jpwattam | | Permalink

David,

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'

You're right!

David Carter | | Permalink

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.

David

Column Widths - I know what you mean!

AnonymousUser | | Permalink

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!

What about column widths

tony.stevens | | Permalink

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.

Tony

Making column widths 'stick' in pivot tables

Geoff Stephens | | Permalink

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.

what version are you on?

David Carter | | Permalink

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.