Pivot Table Issues

Fix Conditional Formatting Not Applying to New Pivot Table Data

Maira Mehtab Sep 28, 2026 870 views

Question details

Conditional formatting rules do not automatically apply to new rows when the source data is expanded and the Pivot Table is refreshed.

Product: Spreadsheet Software
Device & OS: not provided
Scenario: Expanding a source data table, refreshing the corresponding Pivot Table, and expecting conditional formatting to cover the newly added rows.
Observed behavior: New data appears in the Pivot Table successfully, but the existing conditional formatting rules remain restricted to the original fixed cell range and fail to format the new values.

Before you start

Ensure your Pivot Table is completely refreshed and verify whether your current conditional formatting rules are accidentally locked to a fixed cell range (e.g., E6:E18) rather than the Pivot Table field itself.

Solution 1Recommended

Apply Conditional Formatting to the PivotTable Field

Modify the conditional formatting rule so that it is scoped to the PivotTable field rather than a static range of cells, allowing it to expand dynamically.

When applying conditional formatting to a Pivot Table, selecting a fixed range will cause new data to lose formatting after a refresh. Applying the rule to the entire PivotTable field ensures that any new rows pulled from the expanded source data are automatically formatted.

Open the Conditional Formatting Rules Manager

Select any formatted cell within your Pivot Table. Navigate to the 'Home' tab, click on 'Conditional Formatting', and select 'Manage Rules' from the dropdown menu.

Edit the Target Rule

In the Rules Manager window, locate the rule that is failing to expand to new rows (e.g., the rule formatting values between 91 and 364) and click 'Edit Rule'.

Adjust the Rule Scope

At the top of the Edit Formatting Rule dialog, look for the 'Apply Rule To' section. Change the selection from 'Selected cells' to 'All cells showing [Field Name] values'.

Save and Test

Click 'OK' to close the Edit window, then click 'Apply' and 'OK' in the Rules Manager. Refresh your Pivot Table to confirm the formatting now applies to the new rows.

Dynamic Formatting Applied: By applying the rule to the field itself, your conditional formatting will now automatically scale up or down as the Pivot Table refreshes.

Solution 2

Manually Update the 'Applies to' Range

If your spreadsheet version does not support field-level Pivot Table formatting rules, you can manually adjust the cell range.

Manage Pivot Tables Effortlessly

Use WPS Spreadsheet for Seamless Pivot Table Formatting

WPS Spreadsheet provides a highly intuitive interface for managing Pivot Tables and advanced conditional formatting. It allows you to easily apply dynamic formatting rules to entire fields, ensuring your data presentation updates automatically whenever you refresh your source data.

1. Open Your Spreadsheet: Launch WPS Spreadsheet and open the file containing your source data and Pivot Table.
2. Access the Rules Manager: Highlight a cell in your Pivot Table, navigate to the 'Home' tab, and click 'Conditional Formatting' followed by 'Manage Rules'.
3. Apply to Field: Select your rule, click 'Edit Rule', and choose the option to apply the formatting to all cells showing the specific Pivot Table field.

Easily apply dynamic conditional formatting to entire Pivot Table fields.Fully compatible with Microsoft Excel (.xlsx) files and formatting rules.Intuitive Conditional Formatting Rules Manager for quick edits.Free and lightweight alternative for robust data analysis.

Frequently Asked Questions

Why does my conditional formatting disappear when I refresh my Pivot Table?

This usually happens because the conditional formatting was applied to a static cell range (like E6:E18) rather than the Pivot Table field itself. When the Pivot Table refreshes and changes size, the static range remains fixed and does not automatically cover the newly added rows.

How do I make conditional formatting dynamic in a Pivot Table?

To make conditional formatting dynamic, open the Conditional Formatting Rules Manager, edit your rule, and under the 'Apply Rule To' section, select 'All cells showing [Field Name] values' instead of 'Selected cells'.

Can I use formula-based conditional formatting in a Pivot Table?

Yes, you can use formulas (such as checking if a cell is blank) within a Pivot Table. However, ensure that you are using relative cell references correctly so that the formula adapts properly to all cells within the targeted Pivot Table field.

Excel & Data Problems