How can I use Pivot Table References for Conditional Formatting?

Copper Contributor



I have a pivot table where the following columns are repeated for several states, so the easiest way to create the conditional formatting would be one that works for the entire pivot table, even as states are selected and deselected and as both the columns and rows grow and shrink.  I want the "On Hand + On Order" values to be highlighted when they are less than the "Remaining To Ship" value in the same row.  Is it possible to use table references to these columns?  If not, is there a clever / elegant way to write a single Conditional Formatting formula that would cover the entire pivot table as the table expands and contracts (both rows and columns)?




3 Replies


Perhaps the attached example is similar to what you are looking for. I've entered a rule for conditional formatting based on two columns.

Thanks, Oliver. I'm hesitant to download an unknown file; would you mind copying in your conditional formatting rule as text in a response so I can get the general idea and help inform the decision to download your file or not?

Hi @LMercury,

this is the rule for conditional formatting.


And here's a screenshot of the file. I assume that without seeing the screenshot the rule wouldn't be helpful.

conditional format.png