This tutorial demonstrates how to keep formatting consistent on a pivot table in Excel.
Pivot Table Field Number Format
Pivot tables are a great for displaying simplified data from a searchable database in Excel. But a few extra steps are necessary when you want to add formatting to a pivot table. Consider the following pivot table.
To ensure that all the value rows are formatted correctly, amend the number format of the actual field in the pivot table.
- Click in the pivot table. This brings up the PivotTable Fields pane on the right side of the window.
- In the PivotTable Fields pane, select Value Field Settings… from the Values field drop-down list.
- Click Number Format.
- Select Accounting from the Category list, and then select the appropriate currency symbol from the Symbol drop-down list.
- Click OK, and then OK again to return to Excel and apply the formatting to all the value fields in the pivot table. Now if any data is added to the pivot table, the value field retains the format applied.
Format a Column of Data
You can also format the entire column of data that a value field is using in a pivot table.
- Select the entire column (not just the range B4:B6) by clicking on the column header.
- Apply the format.
- Remove the filter to check that the formatting was correctly applied. As you can see below, rows that had been hidden all have the same format.
Preserve Format on Update
Look at the pivot table below. It has been formatted with different background colors.
Now, if you wish to refresh the pivot table to update the data, you can, in the Ribbon, select Data > Queries & Pivots > Refresh All.
As you can see in the above example, all the custom background formatting has been removed and the default colors are reapplied!
- To prevent this from happening (and to keep the custom colors!), in the Ribbon, select PivotTable Analyze > PivotTable > Options.
- Then make sure Preserve cell formatting on update is checked and click OK.
Note: If you also changed the column widths in your pivot table, uncheck Autofit column widths on update to ensure you preserve your custom column widths!