From time to time I’ll answer a question on Stack Overflow about a Conditional Formatting formula that includes an IF statement. Something like this:
=IF(A2=”Something”,TRUE,FALSE)
I’ll politely let them know you don’t need IFs in conditional formatting formulas, and that their formula can be reduced to something like:
=A2=”Something”
I’m going to go out on a limb and say there’s never a need for an IF in a conditional formatting formula.
Why “No IFs?”
What I usually say quickly in my answers is that the IF is implied by the fact that it’s a conditional formula. In other words, when you write the formula you’re telling the conditional formatting:
“If this condition is true, then apply the format, otherwise don’t apply it”. You only need the condition, the rest is a given.
Further, there will never be a reason for a nested IF. Nested IFs only apply when there are more than two possible outcomes. And again, in CF there can only be two: formatting applied or formatting not applied. You might very well, of course, use ORs and ANDs, as those allow you to narrow down the condition:
=AND(A2=”Something”,A3=”Else”)
But it’s still only one condition with two possible outcomes.
Interesting. I hadn’t thought of it that way (that the IF is implied). I had to quickly scramble and look at some conditional formats I’ve done in the past to see what I did. I don’t use a lot of them, so I don’t consider myself to be expert in any way. Fortunately, I didn’t find one IF statement! I do use AND/OR though.
Glad it was informative. I should probably go back and check my own :).
Great article, Doug! I’ll sure link to this in future SO/SU answers/comments! 🙂
Thanks Peter, that means a lot. Interesting URL!
LOL. I honestly have no idea how the URL get there!!! 🙂
You dont need the AND either
(A2=”Something”)*(A3=”Else”) should work fine
True – but in this case it is slightly slower, as excel needs to convert two booleans to numbers, execute the multiplication and then convert it back. AND on the other hand is only one Boolean operation. Not that this would be any notable difference, but just for the records…
Peter,
I haven’t tested this, but I believe that the Operator * would be faster than a function call as it must be working at a much lower level compared to a Worksheet function AND