## How to highlight MAX and MIN value based on month

*Article updated on January 24, 2018*

Conditional formatting formula to highlight max value in every month:

Conditional formatting formula to highlight min value in every month:

**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 overlapping date ranges using conditional formatting

How to highlight overlapping date ranges Click "Home" tab Click "Conditional Formatting" button Click "New Rule.." Click "Use a formula […]Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]Count unique distinct values within same week, month or year

Introduction What are unique distinct values? Unique distinct values are all values but duplicates are merged into one value. Count […]### 7 Responses to “How to highlight MAX and MIN value based on month”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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

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.

Thank you very much Oscar. It is now clear :)

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.

Pradeep,

Conditional formatting formula min value:

Conditional formatting formula max value:

Download excel *.xlsx file

Highlight-max-and-min-value-in-every-month.xlsx

Dear Oscar,

How can I do this for the repeating textual items in column A instead of dates? For example, I would like to determine min values for pears, apples, oranges, whereas the series looks like this:

Pear 5.5

Pear 1.2

Apple 3.2

Apple 5.5

Pear 1.2

Orange 6

Apple 3.2

Orange 5.5

Pear 1.2

Thank you in advance.

Best regards,

Goranka

Goranka

Great question.

Array formula in cell E1:

=MIN(IF(D1=$A$1:$A$9,$B$1:$B$9,""))

Array formula in cell E2:

=MIN(IF(D2=$A$1:$A$9,$B$1:$B$9,""))

Array formula in cell E3:

=MIN(IF(D3=$A$1:$A$9,$B$1:$B$9,""))