I made a pivot table to present three years data and highlighted the numbers > 249 with conditional formatting in the pivot table.
The pivot table is based on three years imported files (2017, 2018, 2019 at the moment).
It's working good, but when I updated the 2019 information, I can see new data that matches this criterium doesn't get the formatting. I thought it might have to do something with the cache, but don't know how to solve it that way.
I can solve it: remove all the filters from the pivot table, reorganize the sorting, refresh the pivot table and make the filtering again, then it works.
But I thought Excel can do this automatically?
thanks for suggestions!
Frans
Hi Frans,
There is a specific way to apply conditional formatting to PivotTables values areas so that it dynamically updates with changes to the PivotTable which is explained here.
Note: this only applies to the values area. Conditional Formatting of row and column labels is not dynamic.
Let me know if you still have problems.
Mynda
Thanks also for this reply Mynda. I read the article you refer to quickly and think I did everything properly. But I'll take a closer look about the extra's you mention after giving the formatting rules. I'll let you know!
Frans
Hi Mynda,
I have implemented your suggestion for dynamic conditional formatting. In my case I wanted to highlight any 0 figures in the PWIN column, snapshot attached. I used to have this field as a row, rather than VALUE to stop it from totalling the numbers in the sub and grand total. To make this dynamic conditional formatting work, following your wizard I have now moved the field to values and the totals are there.
Question: is there a way to stop just this values column from totalling please? I tried expressing the figure as "product" and i tried adjusting the formatting to text rather tyhan number but neither worked.
Hi Allyn,
No, you either have totals on or off for all columns. You could use some conditional formatting to hide the totals by formatting the font in the same colour as the total cell. You'd need to use regular conditional formatting for this, i.e. not PivotTable conditional formatting. Apply it to the column, where the row label contains 'Total'.
If you get stuck, please start a new thread as it will be off topic.
Mynda
