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

Pivot Tip 7 - Tips on formatting a pivot table

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

David Carter's series of Five Minute Tips aims to develop your skill at using Excel pivot tables. To test these formating tips for yourself, try them out on your copy of the pivot_practice.xls database.

The beauty of Excel is that you can format a spreadsheet any way you want. It's not quite so easy in pivot tables, and occasionally you can modify a pivot table only to lose the formatting when you recalculate via the Refresh option. Microsoft has added so many options over the years that it gets confusing. These tips should clarify matters.

Use the black down arrow to highlight columns and rows
At the top of the first column in your pivot table is a grey Field button giving the name of the field in the Row area. Usually it will be in about cell A4; in our example it's called Product or Customer.

If you wave your mouse pointer above this grey field button a thick black down arrow should appear. Left click and it highlights the whole column for formatting.

Similarly, if you wave the down arrow over the other grey field buttons, it will highlight column headings etc too.

Enable Selection
This black arrow automatically highlights an area for you to format. Sometimes, however, the Enable Selection feature gets turned off and the arrow disappears.

To see how it works, right click on the pivot table for menu. Choose Select. At the bottom is the Enable Selection button.

Click on Enable Selection. The down arrow will now no longer appear over the field button.

Now go back and choose Select'Enable Selection. The down arrow re-appears.

How to Activate the other Select options
When you go into the Select menu, you see that the top three options are greyed out. To activate them, click onto Entire Table.

This highlights the entire pivot table. Now go back into Select once more: the three options are now activated. They allow you to highlight part or the whole of the pivot table.

Format a column of numbers
The selection down arrow doesn't work with columns of numbers.

You can manually highlight them with a mouse, then apply formating. However, you might find it quicker to right click on any number in the column, then from the menu select Field Settings'Number.

The formatting you now choose will be applied to all the figures in the column.

Setting column widths
You cannot define the width of a column in a pivot table (if you use Format'Columns'Width the columns will re-set themselves as soon as you Refresh).

Excel estimates the width of the columns from the data or the column heading. So sometimes you have to be a bit inventive to make sure that your columns widths will stay that way even after you refresh.

Making a column narrower
You can type in a new column heading over the existing one.
For example, one of the column headings we created in the last article was 'Sum of Margin Percent', which was far too wide.

Note, however, that if you just remove "Sum of" and change the column heading to 'Margin Percent', Excel will send an error message that 'Pivot table Name already exists' because this is the name of a data field.

Personally, I usually remove 'Sum of' to make a column narrower, but hit the space bar once as well so that the heading is not the same as the field name.

Making columns the same width
One irritating result of Refreshing the data can be that columns all now have different widths.

Sometimes you can control the width of all the number columns by modifying the column heading in the first column.

For example, if the column headings are Apr, May, Jun, Jul etc, go into the first column heading and change Apr to Apr-2005.

Now refresh the data. All the columns become wider, not just the April one.

Format Report
Finally, of course, you can let Excel do all your formatting for you.

Right click on the pivot table to call up the menu, the second option is Format Report. This offers you numerous pre-set formats. Choose any one to reformat the entire pivot table.

Related material in ExcelZone
For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

    Subscribe to the ExcelZone newswire
    To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

  • Tags:

    Replies (1)

    Please login or register to join the discussion.

    avatar
    By jclough
    28th Jul 2005 11:17

    Column Widths
    Thank you for the series of tips - very useful.

    In Excel 2003, there is a tickbox in the Table Options (acessed through the wizard or from the Pivot Table toolbar) called "AutoFormat table". Unticking this box appears to stop the column widths being reset when the data is refreshed.

    Thanks (0)