XL2000: PivotTable Does Not Preserve Conditional Formatting

• Microsoft Excel 2000

SYMPTOMS

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.

CAUSE

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.

WORKAROUND

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

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.

MORE INFORMATION

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.

Additional query words: XL2000

Keywords : xlformat xlpivot
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb