Conditional formatting not updating 2016

Here is the regular way of applying conditional formatting to any dataset: This will apply the conditional formatting as shown below: All the data points which are above the average of the entire dataset have been highlighted.The issue with this method is that it has applied the conditional format to a fixed range of cells (B5: D14).

conditional formatting not updating 2016-52

If you add data in the backend and refresh this pivot table, the conditional formatting would not get applied to it.

For example, I go back to the dataset and add data for one more date (11 January 2015). As you can see in the pic above, the data for 11 January 2015 doesn’t get highlighted (while it should as the values for Store 1 and Store 3 are above average).

Result: Explanation: by default, for 3 icons, Excel calculates the 67th percent and 33th percent.

Change the Types to Number and change the Values to 100 and 0.

Applying conditional formatting in a Pivot Table can be a bit tricky.