How to highlight max and min value in every month in excel

Popular categories

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Get Digital Help > Excel > Conditional formatting > How to highlight max and min value in every month in excel

← Previous post -

Filed in Conditional formatting, Dates, Excel on Sep.08, 2009. Email This article to a Friend

Conditional formatting formula to highlight max value in every month:

=B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, ""))

Conditional formatting formula to highlight min value in every month:

=B2=MIN(IF(MONTH(A2)=MONTH(Date_rng), Close, ""))

Named ranges
Date_rng (A2:A169)
Close (B2:B169)
What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named ranges.

Download excel sample file for this tutorial.
Highlight max and min value in every month.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

MONTH(serial_number) returns the month, a number from 1 (January) to 12 (December)

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

Highlight duplicates on same date, week or month using conditional formatting in excel

Excel: Calculate last date of a specific month

Excel: How to automatically summarize preceding month and year

Remove duplicates within same month or year in excel

Highlight odd/even months using conditional formatting in excel 2007

Recommended categories

Comments (5)

5 Responses to “How to highlight max and min value in every month in excel”

srinivas says:
February 21, 2012 at 4:58 am
Confused Both formulas looks like same. do i have to replace MAX part in second formula with MIN ?? . Also guide me how to use them, where should i insert the formula?
Thank you very much Oscar
srini
Reply
Oscar says:
February 21, 2012 at 10:44 pm
srinivas,
Confused Both formulas looks like same. do i have to replace MAX part in second formula with MIN ??
Yes, you are right! It is now corrected.
Also guide me how to use them, where should i insert the formula?
It is a conditional formatting formula.
How to apply the conditional formatting formula in excel 2007:
1. Select cell range B2:B159
2. Go to tab "Home"
3. Click "Conditional formatting" button
4. Click "New rule..."
5. Click "Use formula to determine which cells to format"
6. Type formula
7. Click "Format..." button
8. Select a color.
9. Click OK twice.
Repeat steps and use the second formula and preferbly another color.
Reply
srinivas says:
February 22, 2012 at 8:56 am
Thank you very much Oscar. It is now clear
Reply
Pradeep says:
August 19, 2012 at 2:42 pm
hey this formula works perfectly for each month but how do I make it do it by year and month? I have about 2600 lines I have to highlight each max and min value for each month of each year.
Reply
- Oscar says:
  August 20, 2012 at 3:12 pm
  Pradeep,
  Conditional formatting formula min value:
  =MIN(IF((YEAR($A$2:$A$92)=YEAR($A2))*(MONTH($A$2:$A$92)=MONTH($A2)), $B$2:$B$92, ""))=$B2
  Conditional formatting formula max value:
  =MAX(IF((YEAR($A$2:$A$92)=YEAR($A2))*(MONTH($A$2:$A$92)=MONTH($A2)), $B$2:$B$92, ""))=$B2
  Download excel *.xlsx file
  Highlight-max-and-min-value-in-every-month.xlsx
  Reply