Use conditional formatting on spreadsheets

Start by loading Excel and creating a simple worksheet (we’ve put together one that shows monthly regional sales figures over the course of a year). Feel free to copy out the example in our screenshot, though the exact figures aren’t important for our purposes, so just make up some numbers of your own. The beauty of conditional formatting is that it can be used to highlight the parts of a spreadsheet that really matter, for example when targets are being hit (or missed). So, we’ll begin by highlighting all the figures in the body of the sheet (not the totals at the bottom) and then click the Format menu and choose Conditional Formatting. (Remember, Excel 2007-specific instructions start at Step 6.)

This opens the Conditional Formatting dialogue box. We want to pick out the months and regions where our target sales of 60 units or above are being met. So, set Condition 1 by leaving the first item (Cell Value Is) alone and choosing ‘greater than or equal to’ from the second dropdown menu and then typing 60 into the third box. Click the Format button then the Font tab, select Bold Italic from the Font style list and then open the Color dropdown menu and choose a strong green shade.

Click OK, then click OK again to confirm the changes. All the figures in the table that meet the condition are now displayed in green, bold and italicised text. This formatting applies to all the cells in the table so if we change the entry ‘23’ in cell E2 to ‘64’ it will automatically take on the new formatting as shown in the screenshot. Let’s do some more. Highlight the table as before and choose Format, Conditional Formatting. We’re going to apply a second condition to our spreadsheet, so click the Add button.

Repeat Step 2 but this time use the ‘less than or equal to’ condition and type in 30. Click Format and select bold and red. Click OK to close. We could add a third condition here (Excel 2003 is limited to a maximum of three conditions applied to any single range) but we’ll leave things as they are for now and instead, set up a brand-new condition for the totals row along the bottom of the sheet. First, click the OK button to close the dialogue box and make the changes.

Next, highlight the totals on the bottom row and choose Format, Conditional Formatting again. It’s a new range so we can apply up to three conditions to it; we’re going to set up ‘traffic light’ colours to show how sales are going. We’ll make sure ‘Cell Value Is’ and ‘between’ are selected and then set the two values as 350 and 450, as these are low sales figures. Click Format and then click the Patterns tab, select red and then click OK. Repeat with value ranges to show average and good sales assigning them orange and green patterns respectively.

Click OK to close the dialogue box. Remove individual conditions via the Conditional Formatting dialogue box by using the Delete button or remove the lot by highlighting the whole table, clicking Edit and then Clear and then Formats. It’s also possible to use formulas as conditions. Try this: highlight the table, click Format, then Conditional Formatting. Select ‘Formula Is’ from the first dropdown menu and then type the formula =MOD(ROW(),2)=0 into the empty box. Click Format, choose the Patterns tab and select a nice orange and click OK. Click OK to produce a stripey orange table.

Here’s the original worksheet in Excel 2007. This newer version of Excel adds many more kinds of conditional formatting but first let’s see how it handles something familiar. Make sure the Home tab is selected on the ribbon bar and then highlight the body of the table again (but not the totals). Click the Conditional Formatting button and then choose Highlight Cells Rules from the menu and then More rules at the bottom. This allows us to set Cell Value conditions just as we did in Excel 2003. Click Cancel.

Alternatively, Excel 2007 offers lots of ‘quick’ conditional formatting options. For example, with the body of the table still highlighted click the Conditional Formatting button and choose Top/Bottom Rules and then Top 10%. Use the up arrow on the spin box to increase the percentage to 20 and then pick Green Fill With Dark Green Text from the dropdown menu. (It’s also possible to create custom colour schemes. Choose Custom Format from list to a dialogue box similar to the one we used in Step 2).

Click OK to confirm the changes. Then repeat Step 8, choosing Bottom 10% and then adjusting it to 20 and picking red highlights. With both the high and low performers highlighted, let’s add traffic lights as we did in Step 5. Highlight the bottom four totals, click the Conditional Formatting button and select Color Scales and then hover the mouse over the first one ­ Green, Yellow Red Color Scale. This automatically assigns traffic light colours to the totals, based on their values.

Now let’s look briefly at some of the other visual styles that can be applied to cells in an Excel 2007 spreadsheet. Here we’ve increased the column widths so it’s more obvious what’s going. We’ve selected the body of the table and then gone for a little tour around the Conditional Formatting menu. Here for example, we’ve selected Data Bars and chosen orange. Excel overlays horizontal bars on each cell to give a visual indication of the content.

Click the Undo button (or use the keyboard shortcut Ctrl and Z) to remove the horizontal bars. Open the Conditional Formatting menu again and this time choose Icon Sets. Roll the mouse pointer around the different icons to see how they’re displayed in the table; like the bars, this is a good way to visualise data in a spreadsheet at a glance. Select the three arrows at the top left of the pop-out menu and see what they look like. Then open the menu again and choose More Rules.

This opens the New Formatting rule dialogue box from where we can amend the defaults used by Excel for the icon sets (less than 33, greater than or equal to 33, greater than or equal to 67 per cent). We can also change the icon style by picking a new one from the dropdown menu, reverse the icon order (useful for things like flagging high-selling stock items that need replenishing) or simply display the icon on its own, good for presentations or when you don’t want to reveal exact figures.

There’s also another option in the list of Rule Types: formulas. Here we’ve created two extra cells for our sales target. When we hit that target the Total should change colour to indicate our success. So, select that cell on the worksheet, choose Conditional Formatting and then New Rule. Select ‘Use a formula’ and then type it into the empty box. Ours is simply =AND(F14>=G14) which changes the format of our total in cell F14 when it equals or exceeds the target in cell G14. Then click Format and define how the cell should look and click OK.

Assuming our sales team has a fantastic December and manages to increase its take by, say, 100 each, we’ll exceed our target and the Total cell (F14) will assume the formatting defined in the previous step. Here we’ve made the adjustments to the sales figures to demonstrate what happens, the cell is highlighted in red with yellow italic text. Finally, Excel 2007 also lets you view and edit any of the conditions associated with a worksheet. Click Conditional Formatting and choose Manage Rules and select This Worksheet to see them in a list.