How to apply conditional formatting formula in MS Excel using Pivot Table values?

0
votes

I have a pivot table and I would like to use conditional formating to visualize better whether Actual is greater or less than LY (Similar to the below).

I have tried using GetPivotData in Conditional Formatting but Excel doesn't seem to like the formula.

This has to be done to all "Actual" values for "Department" and "Month" divided by all respective LY values for Department and Month.

i.e. Actual Amount/LY Amount for all Departments and Months

Any ideas on how to go about this problem?

enter image description here

1
excelpivot-tableconditional-formatting

1 Answers

2
votes
  1. Add the value field to the "Values" area twice.
  2. Select one of the value column and go to "Field Settings" to "Show Value As" Difference From LY
  3. Select one of the value in the "difference" column, add new Conditional Formatting rule
  4. Play with the rule details as show below:

enter image description here