How to specify Pivot Table Conditional Formatting so that the conditional formatting grows and shrinks in line with the pivot table movements.
Pivot Table conditional formatting
One of the new features in Excel 2007 is that Pivot Table conditional formatting now takes into account whether it is or isn’t in a pivot table. If you click on a cell, and you go to conditional formatting – don’t choose one of these now – go to something called New Rule. When you click on New Rule, because you are in a pivot table, some new items will appear here and you’ll see it says Apply the rule to either the selected cell (so only that cell); All cells showing sum of bill values ie those items there; All cells showing sum of bill values for day of week.
So let’s show you- I am just going to choose selected cell for now – I want to do data bars. That’s fine. If I do selected cells and I say OK, you’ll see only that selected cell is chosen.
Let’s go back, go to Manage Rules, edit – if I choose “All Cells showing sum of bill value” and I say OK, you’ll see what it has done is put the data bars in but it has included the grand total which means that this one over powers all the other ones and it is effectively a bit of double counting so that’s probably not what we want. So I go to Manage Rules .
I am going to try this one now – “All cells showing sum of bill values for day of week – when I say OK, you’ll see that is a better representation because what it is doing is just taking the underlying data and not including the grand total. What is nice about this is that now the pivot table aware of the conditional formatting so, for example, if I decide that I am going to remove the day 2 and 3 and I say OK, the conditional formatting will change to address these new options you have chosen. Go to Select All and you have the correct conditional formatting on your pivot table.