Maintaining Formatting when Refreshing PivotTables

Maintaining Formatting when Refreshing PivotTables

PivotTables provide a great way to analyse large amounts of data and pull out the summarisations 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.

Note: when you update the data on which the PivotTable is based, after you refresh your PivotTable, your formatting work may go away.

To prevent loss of format when refreshing follow these steps:

1. Make sure that your PivotTable displays the values you want.
2. Format the PivotTable to your liking.
3. From the PivotTable toolbar, select Table Options from the PivotTable menu. The PivotTable Options dialog box opens. (See Figure 1.)

Figure 1. The PivotTable Options dialog box.

1. Make sure the Preserve Formatting check box is selected.
2. Click OK.

When you now refresh the PivotTable, your formatting should remain on rows and columns previously applied in the PivotTable.

Note: 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.