MS excel provide great way to identify bad or good data quickly with its great feature Conditional formatting (CF). CF have many predefined ways to to format data but sometime problems are even more complicated and predefined procedures doesn’t work.
Here you are going to see one that kind of problem and the solution to that. have a look at the picture below.
So, you will see that there is one table that is linked with the three criteria above. looks easy to do this CF? well, look at the criteria range one more time, that includes the comparison operators too. it means users are allowed to change the expression to format a cell.
Lets do a work around to accomplish this conditional formatting by using Evaluate function.
Excel don’t allow Evaluate to be used on worksheet like other formulas ( Sum, Average etc.), but we can use this in name range. Evaluate can solve any expression that is given in a logical way. so for example.
Evaluate(“2+2”) = 4 , Evaluate(“2=2”)=True
So, i am pretty sure you have got idea how we are going to use this in Conditional formatting, but if not. lets see further.
In order to use Evaluate, we need to build expression dynamically and pass them into evaluate function as name range. didnt get it?, dont worry. Lets say table is started from A7 and first criteria is in B2, so we need to create name range like this.
=Evaluate(A7 & $B$2 &”%”)
=Evaluate(A7 & $B$3 &”%”)
=Evaluate(A7 & $B$4 &”%”)
and name then Green, Yellow, and Red_. make sure you have range A7 selected while you are creating these name range. or else these wont work correctly ( or you will need to fix them later). now at the point, we have name range created that can evaluate expression for any cells and now we need to create CF for this table.
Ok, So select the table and hit ALT + O +D (hot key for CF), select the last option in the list and put the name range there. see screenshot below.
Format it green and hit ok, do the same with all three. once you have created all three conditional formatting, you need priorities and stop them if TRUE. See screenshot below.
you can shift CF rules up and down by using the up and down button and check the checkbox to stop the formatting to go check further conditions if one is found TRUE.
That’s it!! hope you enjoyed this little tutorial. Wants to learn more like this ? contact us to get enrolled in our online training module