Highlight the lowest and highest value in an Excel row or column

Office expert Susan Harkins teaches you two ways to highlight the lowest and highest values in an Excel row or column.



Finding the lowest or highest value in an Excel row or column is simple using the MIN() and MAX() functions, respectively. You just drop in the function and specify the range.

Highlighting the minimum or maximum value within an Excel row or column takes a bit more work. You might consider using the built-in Top 10 Items and Bottom 10 Items rules and changing 10 to 1, but it won't work the way you expect. This rule highlights values for the entire data range; it doesn't consider individual rows or columns. 

There's more than one way to achieve the results you want; the method you choose is up to you. The downloadable demo files (.xls and .xlsx formats) include the Excel workbook used in this article.

The easy way

The easiest way to apply this particular conditional format is to enter the appropriate function and then base a conditional format on the results of that function. For example, let's suppose we want to highlight the lowest price for each part in the sheet (Figure A). As you can see in Figure A, the MIN() functions in column F return the minimum value for each row.

Figure A


After adding the MIN() function, you're ready to add a conditional format rule. Follow these steps.

1. Select the data range. In this case, that's B2:E4.

2. On the Home tab, click Conditional Formatting in the Styles group. In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 5.

3. In the resulting dialog, choose New Rule.

4. In the top pane, select the Use A Formula To Determine Which Cells To Format option.

5. In the lower pane, enter the formula =B2=$F2. In Excel 2003, retain the default condition of Formula Is, and enter the same formula.

6. Click the Format button.

7. In the resulting dialog, click the Fill tab. In Excel 2003, click the Patterns tab.

8. Select a color and click OK. Figure B shows the formula and the formatting preview. 

Figure B 


9. Click OK to apply the new conditional format. As you can see in Figure C, the highlighted value in each row is the same value returned by the MIN() function in column F.

Figure C 


To highlight the highest value, use MAX() instead of MIN(). To find the minimum or maximum value in a column, add MIN() or MAX(), respectively, to the bottom of the data range and use this formula rule in step 5 =B2=B$5.

Minus column F

Adding a new column (or row) usually won't be a problem; if it is a problem, you can hide the new column or row. (I don't recommend hiding data as a rule; it's easy to forget data you can't see.) If you don't want to add a function to the sheet, you can add the function to the rule. To use this method, complete the earlier steps, but in step 5, enter this formula rule =B2=MIN($B2:$E2). (If you're working with the same example sheet, clear the first formatting rule before applying the new one.)

The results are the same, but the rule doesn't rely on the functions in column F. If you delete those functions, the conditional format will continue to work. As before, to highlight the maximum value, use the MAX() function. To evaluate columns instead of rows, use this rule formula =B2=MAX(B$2:B$4).

A monkey wrench

One of the cells in the data range (E3) is blank, because entering a 0 to indicate the vendor doesn't offer this product has an unfortunate impact on the format (Figure D). The conditional format highlights 0, but 0 isn't the lowest price; in this case, 0 indicates that the vendor doesn't offer that particular part.

Figure D 


If you're working with a data set that requires 0, but you want the conditional format to disregard them, use this formula rule =B2=(MIN(IF($B2:$E2>0,$B2:$E2))).

As you see in Figure E, this rule ignores the value 0, but the MIN() function in F3 doesn't. You can use a similar formula in column F as an array =MIN(IF($B2:$E2>0,$B2:$E2)).

To enter the formula as an array, type it in as you normally would, but instead of pressing [Enter], press [Ctrl]+[Shift]+[Enter]; this will display brackets ({ }) around the formula to indicate it's an array formula. Then, copy the array to complete the column (or row).

Figure E


Array formulas are common in complex sheets, so I don't recommend them for the average user; they require specialized knowledge that many users don't have. If you inadvertently change one, you might not have the expertise to fix it. If you're not familiar with using and maintaining array formulas, use the formula rule instead.

What's your favorite Excel tip? Share it with your peers by posting to the discussion.



Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks