Microsoft

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.

 

excel-logo.png

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

ExcelCondFormat_FigA_012314.JPG

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 

ExcelCondFormat_FigB_012314.JPG

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 

ExcelCondFormat_FigC_012314.JPG

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 

ExcelCondFormat_FigD_012314.JPG

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

ExcelCondFormat_FigE_012314.JPG

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.

 

About

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.

9 comments
Naresh429
Naresh429

Assume that I have 5 students names in 1 column and 3 subjects marks in 3 columns. How to apply conditional formatting to highlight student names those who get highest marks in any subject.

rathor1072
rathor1072

Hi

Suppose there are 15 numbers given in a row. I want to find the sum of  top 10 number. How can I do that in Excel?

victorlando
victorlando

Hi Susan

Besides the highlighting, how can I see in a new column the vendor with the better price?

For example, following your figures, I would like that in Cell G2 appears "Vendor2", which is the vendor with the best price.

I will appreciatte your help with this matter.

Best

Orlando

jabm1973
jabm1973

How would I, now that I have the max value in my column highlighted, note that the max is over a certain threshold?   I am the Box Tops For Education coordinator for my daughter's school and the student in each class with the highest total at the end each quarter and at the of the year gets a prize.  If the total for that student is over 400 for the year, then they get an additional prize.


So how would I setup the formula so that I am highlighting differently the max value in the column if it is over the threshold of 400?


I have been stymied by this.  Trying to get an 'AND' clause in the formula


Thank you for any and all help.


Julia

Pepps1234
Pepps1234

How would you exclude more than one value.

So based on this example how would you exclude both 0 and 9.56

gim6182
gim6182

Susan, I learned so many tips and concepts from your column. I understood the Array Formula by selecting each argument separately inside the cell and pressing F9 to see the result.

However, why do you think people never mention the R1C1 notation anymore? I know it was huge for Excel 4 Macro language. But it makes sense (to me only?) when setting up Conditional Formatting or Array Formulas. For example, =IF(C1R="Whatever",TRUE,FALSE) then you know you are testing the "A" column and "any" R (Row) in whatever Range you selected. Or when setting up a Data Validation rule with embedded Name referencing an INDEX/MATCH formula (for example), then the A1 notation (with $ for absolute) looks confusing (to me), more difficult to setup and you need to be very careful about where your active cell is. I hope R1C1 will never become "unsupported" in future versions.

victorlando
victorlando

I already figure it out using MATCH and INDEX :)

gim6182
gim6182

I meant to say "=IF(RC1" not "C1R"

Editor's Picks