Highlight cells according to specified rules by applying conditional formatting

Instructions: If it is easier to work directly in Excel, you can open this in Excel by clicking on the Excel Icon.

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

Conditional Formatting is used to apply different formats to cells (e.g. cell shading/text colour and number format) depending on the value in those cells.

In its simplest form, you can use this to highlight all cells that meet a particular condition, such as highlighting in red all values which are greater or less than a threshold which you specify. This threshold can be fixed or can be made dynamic by linking to a threshold value stored in a cell. Conditional formatting can also be applied to text values, e.g. highlighting “Error” or ”No” values in Red and “OK” or “Yes” values in Green.

The conditional formatting feature also offers additional functionality which can make your workbooks and dashboards particularly powerful. Examples of these include:

• Ranking cell values and Formatting accordingly (e.g. highlighting the values in the top X % in a certain colour)
• Finding unique or duplicate values in a list
• Using Icon sets and Data bars – e.g. creating in-cell chart scales, traffic light symbols, arrows or Harvey Balls to show the how the value in the cell compares to the other values in the range.
• Applying a validation formula to format cells – Ability to create complex [validation formulas] to determine which cells to highlight.

Many of these features are new in Excel 2007 and indicate how Excel can be a powerful and visually appealing reporting tool. One of the main advantages of applying cell shading to conditional formatting rules is that in Excel 2007 when you are filtering a range of cells you can choose to filter by colour, allowing you to easily filter cells by the rules that you have applied.

In this topic we will look at the first two topics above. Icon sets/Data bars and Validation Formulas are covered in separate topics.

To apply simple conditional formatting to a range of cells, the first thing to do is highlight this range. In the Topic Example workbook, if we want to apply conditional formatting to the quantity sold figures, we would highlight cells D2:D8.

With this range selected, we click on Conditional Formatting from the Home Ribbon (under Styles), and then click on ‘New Rule.’

We are presented with the screen shot below. To apply a simple conditional format of highlighting cells greater/less than a threshold value, we select the option ‘Format only cells that contain’.

Say we want to highlight in green all cells greater than our threshold value stored in cell H2 (which is also a named range called ‘Threshold’). So in the ‘New Formatting Rule’ dialog box that appears (screen shot below) we need to change the second drop-down box to the value ‘greater than or equal to’ and in the next box, point to the cell H2 which contains the threshold value. If we have used a named range for the threshold we can also type the name into this box instead of referring to a cell range.

We then click on ‘Format’ and select the cell formatting options we want for the cells that meet this condition. To highlight these cells in a different colour, we select the colour from the ‘Fill’ tab.

We then click ‘OK’ to click out of each dialog box and when we return to our worksheet, the conditional formatting should now be applied. If we then change the threshold value in cell H2 the range of cells which are coloured green should change accordingly.

The other rule types available in the Conditional Formatting dialog box include formatting top/bottom ranked values, values that are above/below average, unique or duplicate values, or using a formula to determine which cells to format.

Using the top or bottom ranked values rule is a good way of visually highlighting the largest or smallest values in a range. When selecting this rule, the below dialog box is shown – you can use the drop-down to select whether to highlight the highest (top) or lowest (bottom) values and then how many values to highlight.

If you leave the default values of Top and 10 then this will highlight the top 10 values in the range you selected. (If there are fewer than 10 values in the range, all will be formatted). Selecting the tick box for ‘% of the selected range’ will select take the number here as a proportion of the count of numbers to work out how many values to format. For example if there are 200 values in the range typing 10 and selecting the tick box will highlight the top 20 values (10% of 200).

Note that the magnitude of these values doesn’t matter – the percentage is based on the number of values, not the scale. If instead you wanted to highlight the top 10% of values by value (rather than count) then you would format the top 10 percentile – either as part of an [Icon set] or using the PERCENTILE function.

Formatting values which are above or below average allows you to split the dataset in half – highlighting the top or bottom half (perhaps each in a different colour).

Selecting to format unique or duplicate values is one of the most useful conditional formatting options when working with large datasets, particularly for example when lookups are being used which may rely on having only unique values.

Highlighting unique values is an alternative to using the Advanced Data Filter option to get a unique list of values (although the conditional formatting option doesn’t allow you to get unique values across multiple columns or copy them to another location). Highlighting duplicate values is an alternative to using COUNTIF for finding out whether a value is duplicated (and therefore potentially whether using a lookup might be appropriate or not).

Using a formula as a conditional formatting rule is useful when you want to highlight values based on more complex rules – for example highlighting a row where the values across multiple columns meet different conditions. See the topic on how to construct a validation formula for further details on how this can be used.

In the Topic Example Workbook, apply conditional formatting to the range of quantity sold (cells $D$2:$D$8) in each of the worksheets according to the applicable rules:

• In the ‘Highlight based on Value’ sheet, highlight cells which are greater than or equal to the Threshold value in cell H2 (also a named range called Threshold)]
• In the ‘Highlight Top 3’ values sheet, highlight the highest 3 values in the range
• In the ‘Highlight Top 10% of values’ sheet, highlight the top 10% of values (Note that this highlights the top 10% of values by count: given that there are only 7 values and 10% of 7 is less than 1, this will only highlight the highest value in the range)
• In the ‘Highlight Duplicate values’ sheet, apply a conditional formatting rule that highlights any duplicate values in the range (the value of 200 is duplicated).

A demonstration of applying simple conditional formatting based on Thresholds is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

Once you have completed the ‘Try it Out’ exercise, the completed Excel file should look like this:

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Excel 2010: Conditional Formatting