Apply Different Conditional Formatting Rules to Excel PivotTable Priority Sections
Question details
Apply separate conditional formatting thresholds to PivotTable values based on whether the row belongs to the High Priority or Normal Priority section.
- Product
- Microsoft Excel PivotTable
- Device & OS
- not provided
- Scenario
- The PivotTable contains priority sections where High Priority values below 7 and Normal Priority values below 6 need different highlights.
- Observed behavior
- A single conditional formatting rule cannot distinguish the priority-specific thresholds, and subtotal values should not be highlighted.
Identify the column containing the Priority label and the column containing the PivotTable value to evaluate. If priority names are blank on repeated rows, set the PivotTable layout to repeat item labels before creating the rules.
Create separate formula rules for High and Normal Priority values
Use this method when each priority section needs its own threshold and subtotal rows must remain unformatted.
Apply both rules to the PivotTable value cells, replacing the sample column letters and row number with the columns used in your PivotTable. The nonblank test excludes subtotal rows from the formatting result.
Select the PivotTable, then go to PivotTable Design > Report Layout > Show in Tabular Form. Choose Repeat All Item Labels so each data row displays its High or Normal Priority label.
Select the cells that show the PivotTable measure, such as the Average of Quality values in column J. Do not include unrelated worksheet cells.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($G3="high",$I3<>"",$J3<7), choose the highlight format, and set Applies to to the selected PivotTable value range.
Create another formula rule from Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($G3="normal",$I3<>"",$J3<6), choose the required format, and apply it to the same value range.
Open Home > Conditional Formatting > Manage Rules and verify that column G refers to Priority, column I is the nonblank detail-field check, and column J is the value being tested. Adjust these references to match your PivotTable.
Use a PivotTable layout that retains priority context
Use this approach if the Priority field is not repeated on every row and formula rules cannot reliably identify the section.
Highlight PivotTable values by priority in WPS Spreadsheet
WPS Spreadsheet lets you build PivotTables and use formula-based conditional formatting to highlight values under different thresholds for High and Normal Priority sections. Use repeated item labels and separate rules to keep the criteria clear and exclude subtotal rows when needed.
- 1. Set up the PivotTable layout: Click the PivotTable and use the PivotTable design or layout options to display rows in tabular form and repeat the Priority labels.
- 2. Select the PivotTable measure: Select the value cells that contain the measure you want to check, such as Average of Quality.
- 3. Create the High Priority formula rule: Choose Home > Conditional Formatting > New Rule > Use a formula, then enter a High Priority rule such as =AND($G3="high",$I3<>"",$J3<7).
- 4. Create the Normal Priority formula rule: Add a second formula rule with =AND($G3="normal",$I3<>"",$J3<6) and choose its formatting style.
Frequently Asked Questions
Why are subtotal rows being highlighted in my PivotTable?
Subtotal rows can meet the numeric threshold even though they are not detail rows. Add a nonblank detail-field condition, such as $I3<>"", to the conditional formatting formula to exclude them.
Do I need to repeat item labels before applying PivotTable conditional formatting?
Repeating item labels is recommended when the formula reads the Priority label from a row cell. Use PivotTable Design > Report Layout > Show in Tabular Form, then choose Repeat All Item Labels.
How do I apply different thresholds to High and Normal Priority rows?
Create two formula-based rules from Home > Conditional Formatting > New Rule. One rule tests the High label and the below-7 threshold; the other tests the Normal label and the below-6 threshold.
Can I use different columns than G, I, and J in the formulas?
Yes. Replace $G3 with the Priority label column, $I3 with a detail column used to identify non-subtotal rows, and $J3 with the PivotTable value column.
