Excel 2010: Conditional Format - Limits to number of Conditional Formats per Tab

[ddcrab]

Hello:

I have an issue with Conditional Formatting in Excel 2010 (actually 2 issues but I believe they are related):

1) I want to change the format of a cell based on an AND logical function with 3 arguments: ex: =AND(A<>"",B="",C=""). However, Excel ignores the 3rd argument and only applies the format based on the first two arguments. I can get around this problem by applying the multiplication approach: =(A<>"")*(B="")*(C=""). However, I have a second format that I want to apply to the same cell based on a combination of AND & OR logical functions: ex: =AND(A="",OR(B<>"",C<>"")). Excel does not evaluate the OR function correctly either. This Tab has 29 Conditional Formats applied, but only 2 to the cells in question.

2) I apply the same Conditional Formatting above in a newly opened Excel Workbook and it is evaluated correctly (all 3 arguments are evaluated for the AND function; the nested AND/OR function works as expected).

My conclusion is that I have reached a limit to the number of allowed Conditional Formats for an Excel Tab. Appreciate any comments / insights.

Thanks,

ddcrab

 

[Jerry Sullivan]

Hi and Welcome to the Board,

Comparing your results in a new workbook is a good trouble-shooting technique; however I think you've drawn an incorrect conclusion from your findings.

Excel 2010 allows up to 64,000 unique cell formats/cell styles in a workbook. I'm not aware of an additional limitation on the number of Conditional Formatting rules; however if one exists it would be much greater than the 29 CF rules you are currently using.

A common cause of unexpected CF results is when the formulas in the rules are not aligned with the Applies to: range references.

For example, if your CF formula is:
=AND($A1<>"",$B1="",$C1="")
and because of headers, you reference the Applies to: $A$2:$C$100
Then the CF formulas in A2:C2 will be formated based on A1:C1; CF formulas in A3:C3 will be formated based on A2:C2 and so on...

It's hard to say from the information you've provided whether that's the cause of your unintended results; however that's a good place to start.

If you find that isn't the cause, then please post an example of an actual CF rule that is not working (both the formula and the Applies to: range reference).

 

[ddcrab]

Hello Jerry:

Thank you for responding to my post. You are correct in that misalignment of Applies to and formula are a frequent culprit in Conditional Format errors and it has been an occasional cause of my errors in the past, but that is not the case this time. The actual formula and range (copied and pasted from Excel is provided below):

Formula 1: =AND($AA7="",$AJ7<>"",$AL7<>"")
Applies To: =$AA$7:$AA$200


Formula 2: =AND($AA7<>"",OR($AJ7="",$AL7=""))
Applies To: =$AA$7:$AA$200

I also agree that it would greatly surprise me that the limit for Conditional Formatting is only 29 items, however, given the new and fully developed workbook examples the only other explanation that I can forsee is corruption of the file. Both the limit and corruption seem highly unlikely, but given my experience with Conditional Formatting (I have many years experience creating complicated spreadsheets with conditional formating) I don't readily see the root cause of this error.

Any other ideas would be welcome.

Thanks,

ddcrab

 

[Jerry Sullivan]

That's interesting. Could you upload a copy of your problem workbook (with any sensitive information removed) to a hosting site like Box.com?

 

[ddcrab]

Hello Jerry:

I am a consultant developing this proprietary model for a client and do not feel comfortable uploading even a scrubbed version (permission to do so would be difficult to obtain and repercussions if I just do it would damage my reputation). Client would say just leave off that condition or do a work-around.

 

[Jerry Sullivan]

I understand your concern and that's certainly up to you; however I believe you could produce an example of the problem that would be completely free of any connection to your client.

For example, if your hypothesis is true that the problem is due to too many conditional formatting rules, try placing 30 - 100 of those CF rules in a new workbook and populate the related cells with "Apple", "Banana", "Cherry"... values to see if the CF works correctly. If it works correctly, try 300-1000 rules.

If you can't replicate the problem starting with a blank workbook, then try deconstructing a copy of the actual problem workbook to get it to the point that the CF doesn't work, but there is absolutely no relationship to your client's project. Step by step you can....

Delete all other worksheets
Delete all names
Remove all non-CF formatting
Delete all values and formulas
Repopulate cells related to the CF with Apple, Banana, Cherry.​

Test the problem cells at each step and if the problem goes away after one step, this should yield a good clue to the cause.

If you have purged everything from the workbook except the CF formulas and Apple, Banana, Cherry data and the problem still persists, I believe that it's reasonable to share a copy of that resulting workbook without feeling that you have jepordized your Client's trust. Of course it's your choice. You are also welcome to send me the file by email if you prefer that to posting to a hosting site. Just send me a PM if that is of interest to you.

 

[ddcrab]

Hello Jerry:

Thanks for idea of deconstructing. I will try it but it may take a while. I have some pressing deadlines on other tasks and this particular issue is of midlevel importance. Will post results, but don't expect it to be too soon (may take a week plus to find the time).