Dressing Up Your PivotTable Design

Once you have a PivotTable created, you may find that Excel’s default formatting is bland at best, unreadable at worst. Here are five formatting options you can adjust when you need to get a PivotTable ready for a presentation or report, but don’t need to create something as visually elaborate as a PivotChart.

Steps shown below apply to Excel 2007 and later. The default PivotTable we are going to work with contains sales and order information and a calculated field that gives us an average of item price by sales person.

Images in this article were taken using Excel 2013 on the Windows 7 OS. To follow using our example below, download PivotTable Design

1. Rename Columns (go to “Unformatted PivotTable” tab to try it yourself!)

When Excel draws your PivotTable, results in the Values area will likely have a title such as “Sum of <field name>“. This column heading is precise but longer and sometimes confusing.

To rename the columns that are created by the PivotTable itself, open menu options for the field you wish to edit and select Value Field Settings. In the dialog box, write your own column name in the Custom Name: textbox and click OK.

2. Change the Number Format (go to “Unformatted PivotTable” tab to try it yourself!)

Sometimes your PivotTable’s default number formats are not what you need to clearly show the information. In our calculated field, for example, we would like that column’s information to be expressed in dollar amounts. You also might want to change the number of decimals that are shown, or highlight negative numbers in red.

To change number formats in a PivotTable, set them in the Value Field Settings dialog box. Click the Number Format button, and then make your choices for the field.

(go to “PivotTable Design Finished” tab to see the finished table)

3. Change Blank Cells to Zeros (go to “PivotTable Zeroes” tab to try it yourself!)

Excel will leave cells that have no data blank in your PivotTable. This can create some visual confusion, especially if there are many blank cells. To change those blank cells into “zeroes”, right-click anywhere in the table and select PivotTable Options from the fly-out menu. Type the number “0” in the For empty cells show textbox. (This doesn’t have to be the number 0, you could also enter a dash or some other character.) Click OK.

Your PivotTable will display zeroes (or the character you typed) in any cell that does not contain data:

4. Change the Layout

The Design tab in the PivotTable Tools contextual ribbon offers you several quick ways to adjust the layout of your PivotTable. If you don’t want “Grand Total” displayed in the table, for example, you can turn them off by selecting options in the Grand Totals dropdown menu in the Layout group.

From this group you can also show or hide subtotals, adjust how multiple fields are displayed in the table, and specify where you’d like to add or remove blank rows.

5. Change the Color

There are several pre-formatted color designs on the Design tab in the PivotTable Tools contextual ribbon in the PivotTable Styles selection box. Choose any to quickly assign colors to your PivotTable’s rows and columns. If you don’t see one you like, click New PivotTable Style at the bottom of the selection box and create a custom style in the New PivotTable Style dialog box. From here, you can specify a number of options such as column, row, and stripe colors in addition to setting fonts for each.

With a little time and a few tweaks, your PivotTable can be ready to send off to your colleagues without hiring a designer.