Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

How to make formatting 'stick' in Excel pivot tables

by
17th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

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 table menu (Format cells - Insert - Delete - Refresh data etc). Select the 'Field' option near the bottom. The Pivot Table Field box appears. Click on Number to the right. Now go ahead and format your numbers.

To format column headings
This is a bit more complicated. Suppose the pivot table has a set of columns for each month - Jan, Feb, Mar, Apr etc - and you want to make them bold, centre them, put in some background colour, etc.

Above the column headings in cell B1 will be a grey cell with the name of the field you have put into the Column area of the pivot table - in this example 'Month'. This is called a field button. If you single left click on the grey field button, that SHOULD highlight all the column headings. You can then proceed to format in the normal way.

If left clicking the grey field button doesn't highlight the column headings, it means you don't have Enable Selection switched on. Right click onto the pivot table, and choose Select. At the bottom, click on Enable Selection. If you now left click on the grey field button, all the column headings should be highlighted.

In Excel 97 there is no indication of whether Enable Selection is switched on or not. But if Enable Selection is switched on in Excel 2000 you see a thick black arrow as you wave your mouse around over the grey field buttons.

You format Rows in the same way - just click on the Row grey field button to highlight all the rows.

Using the Select option
The Select option allows you to highlight parts or all of the pivot table. It is not very intuitive, since to make it work you often have to go into it twice!

When you click on the Select menu only Entire Table and Enable Selection are available - the first three options are greyed out. To un-grey them, click on Entire Table. This will highlight the entire table. Now right click for menu and choose Select again. Now the first three options are available.

Tags:

Replies (10)

Please login or register to join the discussion.

avatar
By AnonymousUser
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)
avatar
By David Carter
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)
avatar
By AnonymousUser
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)
avatar
By AnonymousUser
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)
JPW
By jpwattam
02nd Feb 2004 13:49

Bordering on suicide...
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'

Thanks (0)
avatar
By David Carter
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.

David

Thanks (0)
avatar
By AnonymousUser
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)
Tony Stevens
By tony.stevens
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.

Tony

Thanks (0)
By Geoff Stephens
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)
avatar
By David Carter
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)