Transcripts for the above video clip:

Pivot Tables 2007- Conditional Formatting

One of the new features in Excel 2007 is that 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.