trend analysis and conditional formatting with Excel Pivot Table

Question

I am trying to apply conditional formatting to my pivot table, but can't seem to get it right despite a lot of research.

Let's say there is the following raw data range/table describing how much different sales agents have sold per year broken down into product categories

sales agent - year - product - sales amount

The respective pivot table would have sales agent as a row, year and product as columns and sales amount as the value field.

Now I would like to apply conditional formatting to the value fields depending on a year-on-year comparison. As an example, if the sales agent A has sold more car insurance in 2012 compared to 2011, then the respective cell (sales agent A, 2012, car insurance) should be coloured in green.

Moreover this should also work on a subtotals basis, i.e. if the total sales volume of sales agent A in 2012 is higher than in 2011, then the respective cell (sales agent A, 2012) should be green as well.

I tried to use relative references within the conditional formatting dialog, but this is quite static (i.e. always using the previous column) and doesn't work for all columns nor sub totals.

The other option would obviously VBA, basically walking through the whole pivot table identifying the column and row headers and then using GetPivotData to determine the format. But I would like to avoid that.

Answer 1

you can apply conditional formatting to a complete column in your excel worksheet that will relate to other cells in different ways:

So starting with the 2nd column in the pivot table:

Exclude columns where row 4 has text as there a new product set will start (dont want to compare it to the previous product last years sales!). Also check what you want to check being: is this bigger than last (empty is 0 for this comparison). Finally make sure that the rownumber of the cell itself is bigger than 5 otherwise this will be applied also to for example the year values!!