Day: December 22, 2009

Even though a pivot table is automatically formatted when you create it, you can add your own formatting later. For example, in the pivot table shown below, I’ve added colour to the subtotal rows, and made column B narrow.

However, some of that pivot table formatting might be lost if you refresh the pivot table or change its layout. Even if you select a different item in the report filter, the formatting could be lost.

Here’s what the same pivot table looks like, after I select an order date from the report filter.

Preserve the Formatting

Most pivot table formatting can be preserved if you change the Format options in the PivotTable Options dialog box.

1. Right-click a cell in the pivot table, and click PivotTable Options.
2. On the Layout & Format tab, in the Format options, remove the check mark from Autofit Column Widths On Update. This will prevent column widths from changing, if you have manually adjusted them.
3. Add a check mark to Preserve Cell Formatting on Update
4. Click OK.

Apply Formatting

After changing these Pivot Table options, the formatting should stick. When you apply formatting to the Pivot Table, do the following:

1. Ensure that Enable Selection is turned on.
2. Unless you want to format a single cell, use the pivot table selection technique to select the elements you want to format (point to the top or left edge of the element, and then click when the black arrow appears).

______________

P.S. For more information on Pivot Tables, please see the Pivot Table Tutorials on the Contextures Website.

_________________