Apply Dynamic Conditional Formatting to Excel PivotTable Values
Question details
The user needs conditional formatting in an Excel PivotTable to remain applied as the PivotTable expands, contracts, or refreshes.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- Applying or copying a conditional-formatting rule for PivotTable values that may move or grow after a refresh.
- Observed behavior
- A rule applied only to selected cells may not automatically include newly displayed PivotTable values when the PivotTable layout changes.
Click a value cell inside the PivotTable before opening Conditional Formatting Rules Manager. Confirm that the PivotTable field you want to format is displayed in the Values area.
Apply the rule to all PivotTable cells showing the selected value
Use this method when the conditional formatting should automatically follow a PivotTable value field as rows, columns, or filters change.
PivotTable-specific conditional-formatting scope is more reliable than applying a rule to a fixed cell range. Choose an All cells showing option instead of Selected cells so Excel can update the rule range with the PivotTable.
Click a cell containing the value you want to format in the Excel PivotTable, such as a sales total or count.
Go to Home > Conditional Formatting > Manage Rules. In Show formatting rules for, select This PivotTable when that option is available.
Select the rule and click Edit Rule, or click New Rule. Set the required condition, formula, and formatting style.
At the top of the Edit Formatting Rule dialog, select one of the All cells showing options, such as All cells showing "Sum of Sales" values, rather than Selected cells.
Click OK, then Apply. Refresh the PivotTable from PivotTable Analyze > Refresh to verify that the formatted area adjusts to the current PivotTable results.
Use relative references only when the rule requires a formula
Use this method when your conditional-formatting condition compares each PivotTable value with a related cell or value.
Create adaptable PivotTable conditional formatting in WPS Spreadsheets
WPS Spreadsheets lets you build PivotTables and apply conditional formatting to value ranges, helping you highlight changing results with a familiar spreadsheet workflow. Import existing Excel workbooks and continue editing PivotTable data and formatting without rebuilding the file.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel .xlsx or .xls workbook containing the PivotTable.
- 2. Select the PivotTable values: Click the value cells in the PivotTable that need conditional formatting.
- 3. Create a conditional-formatting rule: On the Home tab, select Conditional Formatting and choose a rule type such as Highlight Cells Rules or New Rule.
- 4. Configure the condition and format: Enter the comparison value or formula, choose the required fill, font, or icon formatting, and confirm the rule.
- 5. Refresh and review the PivotTable: Update the PivotTable data and check that the formatting still highlights the relevant current values.
Frequently Asked Questions
Why does conditional formatting disappear from new rows in my PivotTable?
The rule may be scoped to Selected cells, which is a fixed range. Open Home > Conditional Formatting > Manage Rules and change the rule scope to an All cells showing option for the relevant PivotTable value field.
Do relative references make a PivotTable conditional-formatting range expand automatically?
No. Relative references control how a formula changes for each cell. To make the formatted area follow PivotTable changes, set the rule to apply to all cells showing the relevant value field.
Where can I change the Applies to setting for a PivotTable rule in Excel?
Click a PivotTable value cell, then go to Home > Conditional Formatting > Manage Rules. Set Show formatting rules for to This PivotTable, select the rule, and use Edit Rule to choose the appropriate PivotTable scope.
Can I use Format Painter to copy conditional formatting in a PivotTable?
You can use Format Painter for a quick copy, but it may create a fixed selected-cell rule. For a PivotTable that changes after refresh, edit the rule and select an All cells showing scope instead.
