Conditional Formatting - Any Limits on Formulas

New Contributor

I am using conditional formatting in a very useful way to help with reconciliation between two datasets that are similar but in a very different format.  I have one condition that is working perfectly, but when I try to add another kind of formatting, I'm getting an error (invalid formula) error, despite the fact that the formula seems to work find outside the conditional formatting structure.  


I am pleased with how I've been able to use red text to highlight cells for which there's no corresponding value (based on the row and column headers that uniquely identify that cells contents) in the other source.  The conditional formatting formula is:


Red Text for Non-zero Value having no equivalent on the other side (Working)



Green Text for Perfect Match (Not Working)

=IFERROR(IF(i22=0,0,IF(i22=INDEX(TBLTPVSheet[BillValue],MATCH(1,--(TBLTPVSheet[WPID-PLC]=$F22)* --(TBLTPVSheet[Period-End-Integer]=i$21),0)),TRUE,FALSE)),0)


So my question is not about the specifics of my formulas but rather whether there's some kind of limit to the conditional formatting formulas that I'm hitting up against.  I believe I'm at 156 characters, and up to 256 characters are allowed so that doesn't seem to be the issue.  Any ideas?

2 Replies


You cannot use structured table references such as TBLTPVSheet[BillValue] in a conditional formatting formula. You'll have to refer to the A1-style address of the ranges instead.

Thanks Hans. That's exactly what I needed to hear. I've adjusted my formula to avoid the use of the Table references, and the conditional formatting is now working as desired.

Sidenote: Its annoying that table references are not allowed in conditional formatting, AND further that the Excel conditional formatting editor is not more helpful in helping users to know that this is why the formula doesn't work.