How to Keep Formatting on a Pivot Table in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on April 3, 2023

This tutorial demonstrates how to keep formatting consistent on a pivot table in Excel and Google Sheets


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, set 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, click Value Field Settings… on the Values field drop down.


preservepivotformat value field settings


  1. Click Number Format.


preseverpivotformat number format


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


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, go to 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, go to PivotTable Analyze > PivotTable > Options.


preservepivotformat ribbon


  1. Tick Preserve cell formatting on update and click OK.


preservepivotformat options


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

Tip: Try using some shortcuts when you’re working with pivot tables.

How to Keep Formatting on a Pivot Table in Google Sheets

You can format data in a pivot table in Google Sheets by formatting the column or cells that contain the data. Select the cells in the pivot table, and then in the Menu, go to Format > Number. Choose a format that suits your needs (e.g., Currency).


preservepivotformat gs change format


This applies the chosen formatting to your pivot table column.


preservepivotformat gs formatted


If you remove the pivot table field (here, Order Amount) from that column and add a different field, the formatting remains.

AI Formula Generator

Try for Free

See all How-To Articles