Pivot Table Issues

How to Keep PivotTable Conditional Formatting After Refreshing Data

Amos GikundaAmos Gikunda Sep 30, 2026 872 views

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.

How to Keep PivotTable Conditional Formatting After Refreshing Data
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the entire target column

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.

2
Open Conditional Formatting

Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

3
Enter the formatting formula

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)).

4
Apply and save the rule

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'.

Apply Conditional Formatting to Entire PivotTable Columns
Avoid Cell-Specific Selections: Make sure you do not apply rules only to specific 'Values' cells inside the PivotTable, as this is the primary reason rules break during a data refresh. Always select the entire column.
Efficient Data Analysis

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. 1. Open your data: Launch WPS Spreadsheet and open your existing data file.
  2. 2. Create a PivotTable: Select your data range, go to the 'Insert' tab, and click 'PivotTable'.
  3. 3. Select the column: Click the column letter to highlight the entire column within the generated PivotTable.
  4. 4. Apply formatting: Navigate to 'Home' > 'Conditional Formatting' > 'New Rule' to apply your persistent styles.
Fully compatible with Microsoft Excel (.xlsx) files and formatting rules.Stable PivotTable architecture that preserves data styles and layouts after refresh.Lightweight, fast, and completely free alternative for professional data analysis.
microsoft office alternative - wps office

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.