Did you know that you can associate Conditional Formatting to a Pivot Table? Microsoft has tried to explain this too, but they don’t go into much detail about formulas, and they go into too much detail about Conditional Formatting in general, so it’s hard to focus on what you really need to know. Plus, some of their information is wrong. (hey, it happens!)
So when you create your rule, you will have the option to associate the rule to your Pivot Table in two ways:
- All cells showing “field” values
- All cells showing “Field” values for “field” and/or “other field”
(Wait, hold it, what’s the difference? The second option only comes into play when you don’t want the formatting to apply to a subtotal row)
These options can be super handy but there are a few gotchas:
- If you try to include the values area of the Pivot Table in a rule that doesn’t specify the table, the rule will get mucked up (yes, that’s the technical term) and no longer apply to some rules in the pivot table. Always associate that rule to the Pivot Table when you’re trying to format the values area!
- If you click on the wrong cell when you create the rule (like the 3rd row down in the values area, or the 4th column, etc.) the rule will not work right. (MS doesn’t seem to be clear on this issue! They say only, “Select one or more cells in a range, table, or PivotTable report.” But they are wrong when it comes to writing a formula!
- You can specify a formula for the rule – just make sure you write it with the first value row/column as the base for the formula.
Here’s a slightly less than straightforward example:
Here’s a basic pivot table – it just shows actual work against planned for a number of Princesses. Here’s how I would set it up to make any actuals that are lower than the planned work turn red:
Highlight the first row/column for the Actuals value and create a new rule:
You could also do this if you put the values under rows instead of columns, you would just have to change the formula accordingly.
But wait, what if you only required the Princesses to have actuals if their planned work was more than 10 hours? Or what if you added the Project Names and you only required full actuals reporting for the time the Princesses spent brushing their hair? Yes, all those complex scenarios can be handled by a more complex formula! Like =AND(B4>10,C4<B4) for the first scenario, or =AND(SEARCH(“Hair”,A4),C4<B4) for the second.
I’ll be posting some other magic tricks soon about tricky things you can do with Pivot Tables, so don’t change that dial!