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.
