When you are looking for data in an Excel worksheet you can have Excel do the work for you by using its conditional formatting feature. This lets you format cells in your workbook according to what they contain. So, for example, in a list of invoices you can instantly highlight any that are outstanding more than 60 days or you can identify everyone whose expense account is more than a certain value. It allows you to instantly focus on the information you need without having to find it manually. This month, we’ll look at how to get started with conditional formatting in Excel as well as some great new conditional formatting features in Excel 2007.
Finding values
Let’s start with the example of finding invoices that are outstanding more than 60 days. If your invoice worksheet displays the number of days an invoice is outstanding in column B, select column B by clicking on it. Then, to format the cells so that those outstanding over 60 days are highlighted, in Excel 2003 choose Format > Conditional Formatting. In the first box select Cell Value Is and then choose greater than or equal to and in the last box type 60. Click Format and select a format to use such as filling a cell with a colour and click OK twice. Now any invoices that are outstanding 60 days or more will be highlighted so you can find them easily.
In Excel 2007 you will need to choose the Home tab > Conditional Formatting > New Rule and select Format only cells that contain and you can then use the same options as in Excel 2003.
If you want to highlight the entire row rather than just the cell, you will need to approach the task a little differently. In this case, select the entire range containing your data so, if it starts in cell A3 and continues to cell E300, you would select from A3 to E300. Select the Conditional Formatting option and, if you’re using Excel 2003, choose Formula Is. In the 2007 version, choose Use a formula to determine which cells to format. The formula you will use – assuming that the invoice outstanding period is in column B – is: =$B3>=60.
Now select the Format button and set the format to use for the row. When you click OK, the format will be applied. Conditional formats are live so, if the data in the worksheet changes and you change one value in column B from 30 to 60 the format will be reapplied and the entire row will then be highlighted automatically.
Matching values
Let’s consider the situation where you want to compare two values. For example, you may have a list of employees together with their allowable monthly expenses and the current month’s expenses. If you are interested in knowing which employees have exceeded their monthly allowance you can have Excel do the work. If the allowable expenses are in column C, the actual expenses claimed are in column D and the data starts in row 3, this formula will do the work: =$D3>$C3.
This formula checks to see if the value in cell D of the current row is greater than the value in the corresponding cell in column C and, if it is, it highlights it using the format you specify. Instantly, you will be able to see everyone whose expenses are larger than they should be.
Formatting rows
It is also possible to write a formula that will format alternate rows of a worksheet and which will adapt automatically to rows being added and removed. Select the cells in the worksheet and choose Format > Conditional formatting and select Formula Is and then type: =MOD(ROW(),2)=1
When you select a format to use and click OK, alternate rows of the worksheet will be formatted to match your selected format. Removing a row or adding rows will automatically result in the remainder of the worksheet reformatting accordingly.
Excel 2007 options
Excel 2007 contains many more conditional formatting options than are available in Excel 2003. These include the ability to easily identify duplicate values using conditional formatting. This is useful when you have a list that should contain unique records and where you need to check this. In this case, select the field which should be unique – such as a customer number – and then choose Conditional Formatting > New Rule > Format only unique or duplicate values. Now select Duplicate from the dropdown list, click Format and create a format to use to identify duplicates.
In cell charting
Excel 2007 also includes a handy feature for charting data inside Excel cells. You can use this to provide visual charts showing the relative size of the data in a series of cells. This isn’t completely accurate as it shows some colour even for very small values, but it does give you a handy visual guide. To see this in action, select the series of cells to chart and choose the Home tab > Conditional Formatting > Data Bars and choose one of the data bar options.
You will now see a coloured bar in each cell and its length will indicate the relative magnitude of the value in that cell. There is one thing to be aware of when using this feature and that is that the length of each coloured bar is relative to the other cells you had selected when you added the format to them. So, if you add more numbers to the worksheet later on you must select and format all the cells at once for the bars to render correctly.
There is also an option to use icon sets to format your data. So, if you
have a series of survey questions and answers for example, you can show the answers 1-5 using a range of icons to indicate a certain value. To do this, select the cells containing the values to display and choose Conditional Formatting > Icon Sets and then choose one of the icon sets such as five ratings. This will give you a visual indication of the relative value of the survey question results.