Software

Highlight the minimum and maximum values for Excel subsets

Excel's MIN() and MAX() function work great, until you want to use them on a subset of data. Consider using this conditional formatting rule.

Excel's MIN() and MAX() functions return the minimum and maximum values, respectively in a range. They're easy to implement when evaluating a complete data set,  but what if you need to know the minimum or maximum value of a subset. For instance, let's suppose you want to know the maximum value for each month in the sheet below.

The value 1942 in B6 is the maximum value overall. However, the maximum value for the month of January is 1587, in cell B4. You could use a simple MAX() function for each month, but that's a bit awkward and prone to errors.

If highlighting the value works just as well as returning the value, you're in luck. You can use conditional formatting to highlight the maximum value within each month subset, as follows:

  1. Select A2:B13 and click the Home tab.
  2. Choose New Rule from the Conditional Formatting dropdown in the Styles group.
  3. Highlight the Use A Formula to Determine Which Cells to Format option in the top pane.
  4. In the lower pane, enter the following formula:
    =$B2=MAX(IF(MONTH($A2)=MONTH($A$2:$A$13),$B$2:$B$13))
  5. Click Format.
  6. On the Fill tab, choose a color, and click OK twice. The conditional formatting rule applies the fill color, accordingly!

In Excel 2003, choose Conditional Formatting from the Format menu. In the resulting dialog, choose Formula Is from the Condition 1 dropdown. Enter the formula in step 4. Then, continue with step 5.

Be careful when entering the formula because the mixed referencing is essential to the rule's success. If you're working with a changing data set, use a table so the process is dynamic. (Excel 2007 introduced the table feature, so this won't work in Excel 2003.) To create a table, simply select the range, click the Insert tab, and then click Table in the Tables group. In the resulting dialog, indicate whether your data set includes headers, and click OK.

To highlight the minimum value within the month data set, substitute the MAX() function with a MIN() function-otherwise, the formula is the same:

=$B2=MIN(IF(MONTH($A2)=MONTH($A$2:$A$13),$B$2:$B$13))

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
pradeepbhat92
pradeepbhat92

=$B2=MAX(IF(MONTH($A2)=MONTH($A$2:$A$2521),$B$2:$B$2521)) this formula works perfectly for each month but how do I make it do it by year and month? Right now it just highlights the max in the highest month of all years combined. I have about 2600 lines I have to highlight each max and min value for each month of each year.

jbenton
jbenton

Define a named range "dates" to be =OFFSET($A$1,1,0,COUNT($A:$A)) Define a named range "values" to be =OFFSET(dates,0,1) Fill D2:D13 with 1/1 to 1/12, formatted as MMM (so will appear as Jan-Dec) In E2 enter =MAX((MONTH(D2)=MONTH(dates))*values) as an array (ie with CTRL-SH-enter instead of just enter) Fill this down to E13 Continue to add data to columns A:B for the rest of the year

plcarr
plcarr

When I looked into how this works, I was intrigued by the MONTH($A$2:$A$13) part at the heart of the formula. This returns the Month of the corresponding cell in the same row in MONTH$A$2:$A$13. It's a bit like an array formula without the { }. Is this standard Excel behaviour? I haven't noticed it before.

sparent
sparent

Those solutions seem pretty awkward. My first thought was to use a pivot table. I would simply group the dates by Month and use Max or Min on the Value field.

ssharkins
ssharkins

That was part of my discovery process!

jbenton
jbenton

all conditonal format formulae are treated as array formulae

ssharkins
ssharkins

If all you need are the max and min values, in separate tables, that's a quick and easy solution. If you need the entire data set, a pivot table won't work.

DBlayney
DBlayney

I am a great fan of conditional formatting but I had not realised that formulae are always treated as array formulae. This is an importnat piece if learning for me. What I do not understand is how the MIN function ignores the FALSE values in the implied array where the month does not match. FALSE is numerically zero and lower than the other values.