Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Tom’s Tutorials For Excel: Conditionally Format Five Highest or Lowest Numbers in a List

Here’s how you can utilize Conditional Formatting to identify the highest or lowest numbers in a list. Despite the literal title of this lesson, you can highlight the highest or lowest 3, 21, or any numerical measure in your list of numbers; it need not be a measure of 5.

As you’ll see, the relative formula rule for the 5 highest numbers is
=$B3>=LARGE($B$3:$B$22,5)

If for example you wanted to identify the 8 lowest numbers, the formula would be
=$B3<=SMALL($B$3:$B$22,8)

Being a TRUE / FALSE rule, notice the subject cell's (B3 in this example) absolute reference for the column, because the list of numbers is in column B, and the relative reference for the row, because the list being conditionally formatted extends several rows.

To accomplish this, follow these steps:

Step 1 (all versions of Excel) - -
• Select the range of interest.
• Press Alt+O+D to establish your Conditional Formatting.

IF YOU ARE USING EXCEL VERSION 2003 OR BEFORE:

Step 2 (version 2003 or before) - - In the Conditional Formatting dialog box:
• From the drop down list, select Formula Is.
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 3 (version 2003 or before) - - In the Format Cells dialog box:
• Go to the Patterns tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 4 (version 2003 or before) - -
In the Conditional Formatting dialog box, click OK, and you are done.

IF YOU ARE USING EXCEL VERSION 2007 OR AFTER:

Step 2 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box:
• In the "Show Formatting Rules for" field, select Current Selection.
• Click on the item labeled "New Rule".

Step 3 (version 2007 or after) - - In the New Formatting Rule dialog box:
• Select "Use a formula to determine which cells to format"
• Enter your formula, which in this example is =$B3>=LARGE($B$3:$B$22,5)
• Click the Format button.

Step 4 (version 2007 or after) - - In the Format Cells dialog box:
• Go to the Fill tab.
• Select a color from the palette. I chose yellow.
• Click OK.

Step 5 (version 2007 or after) - -
In the New Formatting Rule dialog box, click OK.

Step 6 (version 2007 or after) - -
In the Conditional Formatting Rules Manager dialog box, click OK, and you are done.