About this lesson
Understanding the tips and tricks to get conditional formats working properly on Pivot Tables
Download this lesson’s related exercise files.Applying conditional formats to Pivot Tables.xlsx
49.8 KB Applying conditional formats to Pivot Tables - Completed.xlsx
Applying Conditional Formats to Pivot Tables
Configuring conditional formats to work with Pivot Tables.
When to use
Use to add conditional formats to a Pivot Table.
Creating a Conditional Format
- Select a cell within a value column that you wish to use for your conditional format
- Go to Home --> Conditional Formatting and add the conditional formatting rule you need
Applying the cell to the rest of the PivotTable
- Click the formatting widget and choose to apply to:
- The selected cell(s) only
- All cells showing [measure] values (All rows including totals)
- All cells showing [measure] values for x (Excludes totals)
- If you cannot locate the widget, go to Conditional Formatting --> Manage rules and edit the rule. The options listed above will be at the top.
Hints & tips
- Create separate rules for detail and subtotals, applying them each to the final option
- When you collapse a subtotal section, the rule will be hidden with the detail rows
- Try to create rules for detail and subtotal lines that use differing colors, so it is obvious which rule is being examined
Lesson notes are only available for subscribers.