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

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.

You are here: Home >> Excel >> Conditional formatting >> How to highlight max and min value in every…

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.

srinivas Says:
February 21st, 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
Oscar Says:
February 21st, 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.
srinivas Says:
February 22nd, 2012 at 8:56 am

Thank you very much Oscar. It is now clear