See all How-To Articles

How to Keep Formatting on a Pivot Table in Excel

This tutorial demonstrates how to keep formatting consistent on a pivot table in Excel.

 

preservepivotformat intro

 

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.

 

preservepivotformat unformatted

 

In the example below, there’s a filter on (1) Region, and the figures in the Sum of Order Amount field are (2) formatted using the Accounting Format button in the Ribbon.

 

preservepivotformat format boston

 

However, if you remove the filter from Boston, the rest of the rows in the pivot table are not formatted with the currency symbol!

preseveformat no format

 

To ensure that all the value rows are formatted correctly, amend the number format of the actual field in the pivot table.

  1. Click in the pivot table. This brings up the PivotTable Fields pane on the right side of the window.

 

preservepivotformat show fields

 

  1. In the PivotTable Fields pane, select Value Field Settings… from the Values field drop-down list.

 

preservepivotformat value field settings

 

  1. Click Number Format.

 

preseverpivotformat number format

 

  1. Select Accounting from the Category list, and then select the appropriate currency symbol from the Symbol drop-down list.

 

preseverpivotformat format cells

 

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

  1. Select the entire column (not just the range B4:B6) by clicking on the column header.
  2. Apply the format.

 

preserveformat select column

 

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

 

preservepivotformat intro

 

Preserve Format on Update

Look at the pivot table below. It has been formatted with different background colors.

 

preservepivotformat changed format

 

Now, if you wish to refresh the pivot table to update the data, you can, in the Ribbon, select Data > Queries & Pivots > Refresh All.

 

preservepivotformat refresh data

 

As you can see in the above example, all the custom background formatting has been removed and the default colors are reapplied!

  1. To prevent this from happening (and to keep the custom colors!), in the Ribbon, select PivotTable Analyze > PivotTable > Options.

 

preservepivotformat ribbon

 

  1. Then make sure Preserve cell formatting on update is checked and click OK.

 

preservepivotformat options

 

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!