Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Formatting is not retained in PivotTable in Excel

View products that this article applies to.


When you pivot fields or update data in a PivotTable, the formatting is cleared.

↑ Back to the top


This problem occurs if you update data or change the layout of the table when the following conditions are true:
  • You click PivotTable on the PivotTable toolbar, point to Select, and then click Enable Selection (you deselect the option)
  • You clear Preserve Formatting in the PivotTable Options dialog box.
  • You format data in an existing PivotTable.

↑ Back to the top

More information

Microsoft Excel 97 includes "persistent" formatting. This means that formatting in PivotTables remains intact through pivots and updates. The formatting is tied to items and fields in the PivotTable that cause formatting to be removed when data is updated or fields are pivoted. To bind formatting to a selection of cells, select items using "structured" selection.

Structured selection allows you to select PivotTable areas, labels or data based on the data structure (for example, an item or field). This allows you to select different parts of the data that is associated with each item or field before you perform another action, such as formatting or data analysis. Structured selections are of three types: data, labels, or labels and data.

To make structured selection available, click PivotTable on the PivotTable toolbar, point to Select, and then click Enable Selection.

Note: Microsoft Excel does not retain changes to cell borders when you change the layout or refresh a PivotTable.

↑ Back to the top

Keywords: KB162028, kbualink97, kbprb

↑ Back to the top

Article Info
Article ID : 162028
Revision : 3
Created on : 1/19/2007
Published on : 1/19/2007
Exists online : False
Views : 320