Conditional Formatting: numbers and text

Conditional formatting has been a feature of Excel for quite a while. However, until Excel 2007 it was a severely restricted feature. What you can do with CF is to say, for example, if the result of a calculation in a cell or range of cells is X then change the font colour to red, make the cell background green and make the font italic.

CF can take arguments formulas and so on.

Excel 2007 has taken away many of the restrictions that Excel up to and including Excel 2003 had lumbered us with.

So, take a serious look at CF in Excel 2007 if nothing else: this topic is covered under various headings in my up and coming boo, Excel 2007 with Excel Master.

Here is a problem I faced today. I was given an absence report for a section of our employees and they were either absent or not for a given number of days in a given period. Moreover, as some of them may not have been employed during that period they were marked as NA during such a period. I wanted to flag the difference between authorised and unauthorised absence: two separate columns so that is easy. But, NA would be in both columns AND NA is text as opposed to the number of days recorded as absent: from 0 to 28, 30 or 31 days for a month.

To cut a long story short, here is how I conditionally formatted a column of data that included both values and text. Here is one month’s data … shown after CF has been applied:


Start by choosing CF from the Home tab and clicking New Rule

Now choose

Format cells that contain:

cell value … between =1 and =31 and then format the formatted cells as you wish:


Click OK and you should see this and again click OK and your values greater than zero, up to 31 should be formatted but not any zero value and not the NA text entries.


Marvellous: not so easy with Excel 2003!