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