XL2000: PivotTable Does Not Preserve Conditional Formatting

When you refresh or pivot fields in your PivotTable report, conditional formats do not move with the field(s) to which they are applied. Instead conditional formats remain in the same cells to which they were originally applied.

This behavior occurs when you apply a conditional format to any cell within a Microsoft Excel PivotTable report. Because conditional formatting applies to cells and is not attached to a PivotTable field, when you refresh or pivot the layout of your PivotTable report, the conditional formatting remains with the cells to which it was originally applied.

To apply conditional formatting to different cells in your PivotTable, you must add the conditional formatting as you did to the original cells. For more information about using conditional formatting, refer to Microsoft Excel Help.

NOTE: Using the Copy and Paste Special Formats command to copy a cell containing conditional formatting, also causes all formatting including borders, shading, and fonts to be pasted. This results in more formatting applied than you may expect.

To remove formatting from cells that do not need conditional formats, follow these steps:

1. Select the cells from which you need to remove the conditional formatting.
2. On the Format menu, click Conditional Formatting.
3. In the Conditional Formatting dialog box, click Delete. If you have multiple conditions, click to select the check box for each condition you want to delete. Click OK.
   
   The Preview of format to use when condition is true box should now show "No Format Set." Click OK.

Finding Cells Having Conditional Formats

If you do not know which cells within your PivotTable may have conditional formats applied to them, follow these steps to select all cells having conditional formatting:

1. On the Edit menu, click Go To. In the Go To dialog box, click Special.
2. In the Go To Special dialog box, click Conditional formats. Click OK.

You can apply conditional formatting to cells to show formatting based upon the cell's value meeting a specified criteria. When the cell does not meet the criteria, the formatting is suppressed. Conditional formats remain applied to the cell until you remove them.

The Preserve Formatting option for PivotTable reports retains any formatting you apply to PivotTable report data when you refresh the data or change the report's layout. However, this option does not apply to conditional formatting. If you apply conditional formatting to cells within a PivotTable report, you get unpredictable results if your data changes size or if you pivot fields in the report.