본문 바로가기

MS Office/Excel

Pivot table name already exists.

Pivot Tip 7 - Tips on formatting a pivot table

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.xlsdatabase.

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

  • http://www.accountingweb.co.uk/topic/technology/pivot-tip-7-tips-formatting-pivot-table

    'MS Office > Excel' 카테고리의 다른 글

    엑셀 실무활용하기  (0) 2014.04.16