Excel Guides

Maintaining Formatting when Refreshing Pivot Tables in Excel

When you refresh a pivot table in Excel, any formatting that you have applied to the pivot table may be lost. This is because the process of refreshing a pivot table replaces the old data with new data, and also removes any customizations that have been made to the pivot table. In order to maintain your formatting when refreshing a pivot table, you will need to reapply your formatting after each refresh.

There are a few different ways that you can go about doing this. One option is to use the "PivotTable Options" dialog box to specify that your formatting should be preserved when the pivot table is refreshed. To do this, simply select the "Preserve cell formatting on update" checkbox within the "PivotTable Options" dialog box. Your formatting will then be preserved each time you refresh the pivot table.

Another option is to use VBA code to preserve your formatting. This approach requires a bit more work, but it can be very effective. The code below can be used to preserve your formatting when refreshing a pivot table:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) 
Dim pt As PivotTable 
Set pt = Target 
pt.Format xlAll 
End Sub

This code will need to be added to the worksheet that contains your pivot table. Once it has been added, your formatting will be preserved each time you refresh the pivot table.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.