Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Maintaining Formatting when Refreshing PivotTables.
by Allen Wyatt
(last updated June 24, 2017)
PivotTables provide a great way to analyze large amounts of data and pull out the summarizations that you need. Once you have the PivotTable displaying the values you need, you can then format the table to make the data presentable—for a while. You see, when you update the data on which the PivotTable is based, and then refresh the PivotTable, all your formatting work may go away.
The way around this is to follow these steps:
Figure 1. The Layout & Format tab of the PivotTable Options dialog box.
Now, when you refresh the PivotTable, your previously applied formatting should remain on rows and columns previously in the PivotTable. If the refresh results in new rows being added to the PivotTable, then you will still need to format those, unless you are using an AutoFormat.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8731) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Maintaining Formatting when Refreshing PivotTables.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Wonder what happened to the data behind a PivotTable? It could be in a number of places, and tracking it down could be a ...Discover More
If you modify the data on which a PivotTable is based, you'll need to refresh the table so it reflects the modified data. ...Discover More
Wish there was a way to define how you want PivotTables formatted before you actually create the PivotTable? You may be out ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.