How to Keep PivotTable Conditional Formatting After Refreshing Data
Question details
The user needs to maintain conditional formatting rules on a PivotTable after the data source is refreshed to prevent the styles from shifting or disappearing.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Applying conditional formatting to PivotTable data and subsequently refreshing the underlying data source.
- Observed behavior
- Conditional formatting rules shift to incorrect cells, move, or completely disappear when the PivotTable is refreshed.
Ensure your PivotTable is fully updated with the latest data source before applying new formatting rules, and clear any existing broken conditional formatting rules in the target columns.
Apply Conditional Formatting to Entire PivotTable Columns
Creating a separate rule for each specific column and selecting the entire column before applying the formula prevents the formatting from breaking upon refresh.
When a PivotTable refreshes, the data range expands or contracts. If conditional formatting is applied to specific cells rather than the structural column, the rules get misaligned.
Click on the column header letter (e.g., Column B) directly above the PivotTable to select the entire target column, rather than just highlighting the values.
Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.
Choose the option 'Use a formula to determine which cells to format'. Enter a formula that checks for both your condition and a number to avoid formatting headers, such as =AND(B1>128,ISNUMBER(B1)).
Click the 'Format' button to choose your desired font or fill styling, click 'OK', and then test the rule by right-clicking the PivotTable and selecting 'Refresh'.

Manage PivotTables and Formatting Easily with WPS Spreadsheet
WPS Spreadsheet provides robust PivotTable features with highly stable conditional formatting that seamlessly adapts to data refreshes. It allows you to build dynamic reports without worrying about losing your custom styling.
- 1. Open your data: Launch WPS Spreadsheet and open your existing data file.
- 2. Create a PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable'.
- 3. Select the column: Click the column letter to highlight the entire column within the generated PivotTable.
- 4. Apply formatting: Navigate to 'Home' > 'Conditional Formatting' > 'New Rule' to apply your persistent styles.

Frequently Asked Questions
Why does my conditional formatting disappear when I refresh a PivotTable?
This happens because the conditional formatting is tied to absolute cell references rather than the PivotTable's logical structure. When data expands or contracts upon refresh, those specific cells may no longer contain the data you originally intended to format.
Can I use standard formatting instead of conditional formatting to avoid this?
Yes, standard formatting applied via the built-in 'PivotTable Style' options found under the PivotTable Tools tab will persist after a refresh. However, this method will not dynamically highlight specific data based on varying cell values.
What does the formula =AND(B1>128,ISNUMBER(B1)) do in this context?
This formula checks two conditions simultaneously: it ensures the cell value is greater than 128, and it verifies that the cell actually contains a numeric value (ignoring text headers or blank cells). This prevents unwanted formatting from being applied to the entire column.




