Conditional formatting (CF) is one of those things in Excel that you start using and you realize that it has a lot of potential. If we only knew or remembered how…!
CF requires a bit of practice, often. And some tips and tricks will also help. Here are some tips that may help you take CF to the next level.
My posts don’t replace the CF online help (always good to check..!) These are just some tips that I find useful, and may help a lot to know upfront.
Get acquainted with the CF rule manager
Notice that you don’t need to have any particular range selected when you open the rule manager. You can do that from the rule manager.
Check for a condition over a broad range of cells or columns
Click on “new rule” , select “use a formula…“
To begin with, you can select a bunch of columns, starting at “A”.
Before working on the formula, open the “Format” dialog, and set the color fill for the cells that meet the condition. For now that will be enough.
Type in the formula or condition that you want to test. Start with a very easy-simple one, and refer it to A1. For example, here I’m going to check if the contents are equal to 5.
Important: by using A1 without the “$“, you will test the condition over the entire range. As you start using more advanced or complex conditions or formulas, you need to keep this in mind.
When you go back to the main rule manager window, make sure the “Applies to” range still refers to what you had in mind, for example: =$A:$M
(Things not working? Sometimes Excel plays tricks on us by changing the references without us knowing…)
In my dummy data set, the results look like:
Way too simple!? Now you can start playing with your formula to look for more interesting stuff… Lets say we want to highlight cells that contain text.
Look at the results… Oh! It looks like we have some hidden text in B2..!
Interesting. Could there be more surprises? Lets change the formula and use ISNUMBER instead.
And the result is:
Cells that have numbers are highlighted, and second surprise, there is a cell that had a number in white font (100) !
The examples are very simple. But if you work your way from here you will see that it is very easy to use different formulas to check for more advanced and complex conditions. Just look at how many “IS..” formulas you have that check for conditions:
Go on and enjoy! And subscribe (top right) for Part 2
