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:

condit_format_values_text_1

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:

condit_format_values_text_3

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.

condit_format_values_text_2

Marvellous: not so easy with Excel 2003!

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: