How to specify Pivot Table Conditional Formatting so that the conditional formatting grows and shrinks in line with the pivot table movements.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
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.