Does anyone know how to preserve formula based conditional formatting on Pivot Table values?
I would like to be able to put a colour fill on all rows where a Row Field has a specific value - eg where an issue date is "01/02/2021"
If I apply the formatting and then refresh, the formatting persists on the Row Field columns but is lost on the Values columns.
I read somewhere that this was a limitation back in Excel 2010 that could only be overcome with VBA - which I cannot abide - so was wondering if this can now be done somehow in Excel 365?
Any thoughts greatly appreciated.
Dave White, Bristol England
Hi Dave,
There is a setting in the Conditional Formatting rule dialog box that allows you to apply the rule to 'all cells showing...' based on the PivotTable. See this tutorial.
Mynda
Thanks as always, Mynda.
It is pretty rubbish that Microsoft have not fixed the conditional formatting of pivot tables so you can highlight and entire line in a report when criteria are met. The changed in the "applies to" range after each refresh are a nightmare.
I will see if I can achieve the desired result with two conditional formatting rules, one for the values and one for the labels.
Wish me luck!
Yeah, I agree it's not ideal. Hope you find a workaround.
