1. Auto formatting
Excel comes with a built in range of styles that can be used. To apply one of these styles to your pivot table
- click any cell in the pivot table
- rom the ‘PivotTable tools/Design ribbon select one of the styles from the drop down section on the right
2. Drag and drop fields to new positions in your pivot table
Sometimes you have many fields (columns in your data) – for example Profit and Loss headings. The ‘pivot table field list’ dialogue box may not be able to show all of the fields you have selected. Therefore, it may be difficult to reorder in the dialogue box.
As an alternative you can drag and drop items around your pivot table. In your pivot table:
- Select any row heading and select the edge of the cell and simply move up or down as required
- Similarly, for columns select any column heading and select the edge of the cell and simply move right or left as required
NB be aware that changing a pivot table like this can be addictive and you can fall into the trap highlighted in the blast point in the basics section
3. Rename fields for better readability
When you add fields to a pivot table, the pivot table will display the name that appears in the source data. Value field names will appear with, for example, “Sum of ” or “Count of” when they are added to a pivot table. E.g. you’ll see Sum of Sales, Count of Region, and so on. This does not look great on a report that you wish to publish.
To change the names you can
- Change the name to something meaningful using the field settings dialogue box (best practice)
- An alternative would be to simply overwrite the name with your own but on occasion this can be less stable as it can revert to the original. Just select the cell that contains the field you want to rename and type a new name.
- NB when you view the field settings dialogue box you will see both the original source name and the custom name that you have given the field.
4. Add a space to field names when Excel complains
When you try to rename fields, you will run into a problem if you try to use exactly the same name as one that already appears in the data.
- For example, suppose you have a field called Sales in your source data. As a value field, it appears as Sum of Sales, but (sensibly) you want it to say Sales. However, when you try to use Sales, Excel give you an error message saying “PivotTable field name already exists”.
- As a simple workaround, just add a space to the end of your new field name. You can’t see a difference, and Excel won’t complain.
5. Format empty cells
If you have a pivot table that has a lot of blank cells, you can control the character that is displayed in each blank cell. By default, empty cells will display nothing at all. To set your own character,
- right-click inside the pivot table and select Pivot Table options.
- Then make sure that “Empty cells as:” is checked, and enter the character you want to see.
- Keep in mind that this setting respects the applied number format. For example. if you are using the accounting number format for a numeric value field, and enter a zero, you’ll see a hyphen “-” displayed in the pivot table, since that’s how zero values are displayed with the Accounting format.
6. Tabular format is a neat way to display data with multiple descriptive columns
Often you will want to show several descriptions against a row in your pivot table e.g. in a sales table you may wish to show region, division and product. The default setting is to show items in outline form which would show these on separate rows:
- Region X
- Division 1
- Product ABC
- Product DEF
- Division 2
- Product ABC
- Product DEF
- Region Y
- Division 1
- Product ABC
- Division 2
- Product ABC
- Product DEF
To change this for each field
- open up the ‘field settings’ dialogue box and select the ‘layout and print tab’
- on this tab check the ‘Show items in tabular form’ and ‘repeat item labels’ boxes.
- Do this for all descriptive columns you require
- You will end up with a much more compact report which looks like:
| Region | Division | Product | Value |
| Region X | Division 1 | Product ABC | |
| Region X | Division 1 | Product DEF | |
| Region X | Division 2 | Product ABC | |
| Region X | Division 2 | Product DEF | |
| Region Y | Division 1 | Product ABC | |
| Region Y | Division 2 | Product ABC | |
| Region y | Division 2 | Product DEF |
7. Add a little white space around your pivot tables
This is just a simple design tip. A generally accepted principle of good design is that a pleasing design requires a little white space. White space just means empty space set aside to give the layout breathing room. To do this:
- After you create a pivot table, insert an extra column to the left and an extra row or two at the top. This will give your pivot table some breathing room and create a better looking layout.
- Make sure you have gridlines switched off (deselect the check box from the view ribbon)
- In most cases, I also recommend that you turn off gridlines on the worksheet.
- The pivot table itself will present a strong visual grid, so the gridlines outside the pivot table are unnecessary, and will simply create visual noise.
8. Turn off AutoFit when necessary
By default, when you refresh a pivot table, the columns that contain data are adjusted automatically to best fit the data. Normally, this is a good thing, but it can drive you crazy if you have other things on worksheet along with the pivot table, or if you have carefully adjusted the column widths manually and don’t want them changed.
To disable the auto fit feature, right-click inside the pivot table and choose PivotTable Options. In the first tab of the options (or the layout tab on a Mac), uncheck “AutoFit Column Widths on Update”.
9. et rid of useless headings
The default layout for new pivot tables is the Compact layout. This layout will display “Row Labels” and “Column Labels” as headings in the pivot table. These aren’t the most intuitive headings, especially for people that don’t often use pivot tables.
An easy way to get rid of these odd headings is to switch the pivot table layout from Compact to Outline or Tabular layout. This will cause the pivot table to display the actual field names as headings in the pivot table, which is much more sensible.
To get rid of these labels altogether, look for a button called Field Headers on the Analyse tab of the Pivot Table Tools ribbon. Clicking this button will disable headings completely.
10. Get rid of row and column grand totals
By default, pivot tables show totals for both rows and columns, but you can easily disable one or both of these totals if you don’t want them.
- On the Pivot Table tab of the ribbon, just click the Totals button and choose the options you want.
Excel Monkey 