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

The image above shows rows highlighted based on value in column C being the largest or smallest in that particular month.

Conditional formatting formula (Max)

Conditional formatting formula (Min)

### Explaining CF formula (Max)

Step 1 to 3 make sure that values from the correct year and month are extracted based on the date of the current row.

#### Step 1 - Compare year to dates

The YEAR function returns the year from an Excel date.

YEAR($B$3:$B$16)= YEAR($B3))

becomes

YEAR({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})= YEAR(41141))

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}.

#### Step 2 - Compare month to dates

The MONTH function returns a number representing the month from an Excel date.

MONTH($B$3:$B$16)=MONTH($B3)

becomes

MONTH({41141; 41139; 41131; 41127; 41124; 41121; 41120; 41117; 41116; 41107; 41103; 41095; 41093; 41117})=MONTH(41141)

and returns

{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 3 - Multiply arrays

Both conditions must be met in order to extract the number, we apply AND-logic if we multiply the arrays.

(YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}*{TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

and returns

{1;1;1;1;1;0;0;0;0;0;0;0;0;0}.

#### Step 4 - Convert boolean values

The IF function returns row number if the logical expression evaluates to TRUE and nothing "" if FALSE.

IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, "")

becomes

IF({1;1;1;1;1;0;0;0;0;0;0;0;0;0}, $C$3:$C$16, "")

and returns

{3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""}.

#### Step 5 - Get largest number from array

The MAX function returns the maximum value from a cell range or array.

MAX(IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, ""))

becomes

MAX({3579; 3082; 6883; 3588; 332; ""; ""; ""; ""; ""; ""; ""; ""; ""})

and returns 6883.

#### Step 6 - Compare the largest value to current value

The equal sign compares the values and returns TRUE if they match.

MAX(IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, ""))=$C3

becomes

6883=3579

and returns FALSE. Row 3 is not highlighted.

### Get Excel *.xlsx file

### Cf dates category

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This article demonstrates event code combined with Conditional Formatting that highlights overlapping date ranges based on the selected date range. […]

Color odd months Conditional formatting formula: =MOD(MONTH($B6),2) Explaining CF formula in cell B6 Step 1 - Calculate number of month The […]

The following conditional formatting formula highlights dates based on day of the week. =TEXT(B3,"DDDD")=$D$3 The TEXT function converts a value […]

Cell range B3:B14 has conditional formatting applied, the formula checks if the date is today. Conditional formatting formula: =TODAY()=B3 A […]

The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]

### Cf min max category

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

Question: How do I highlight the smallest duplicate value in a column using conditional formatting? Answer: Conditional formatting formula in […]

### Conditional formatting category

The image above demonstrates a conditional formatting formula that colors a record if there is at least one record that […]

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]

adam asks: Hi, I have a situation where I want to count if this value is duplicate and if it […]

Question: How do I highlight dates that meet criteria using conditional formatting? Table of contents Highlight values in a column […]

This post demonstrates how to highlight records with the closest value to a criterion, you can also choose to highlight […]

This article shows you how to easily identify duplicate rows or records in a list. What's on this webpage Conditional […]

The image above demonstrates a conditional formatting formula applied to an Excel Table containing random data. The Excel Table has […]

Here is how to highlight every other row using conditional formatting. Conditional formatting formula: =ISEVEN(ROW())*OR($B3:$D3<>"") Alternative CF formula: =EVEN(ROW())=ROW() This […]

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

The image above shows you how to highlight rows with multiple criteria using OR logic. The criteria are found in […]

In this article, I will demonstrate how to search a table using conditional formatting. The criteria highlight matching column and […]

Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]

The image above demonstrates a conditional formatting formula that highlights duplicate items based on date. The first instance is not highlighted, […]

A conditional formatting formula highlights values in column B that also exist in column D. =COUNTIF($D$3:$D$7,B3) The same thing happens […]

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

The image above shows conditional formatting highlighting unique distinct values, duplicates are not highlighted. Conditional Formatting Formula: =COUNTIF($B$3:B3, B3)=1 The […]

This article demonstrates how to apply different cell formatting to a cell range based on a Drop Down list, column […]

In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The […]

The image above demonstrates a conditional formatting formula that highlights records that only exist in one table. There are two […]

## Functions in this article

More than 1300 Excel formulas

## Conditional Formatting categories

## Excel categories

### 9 Responses to “How to highlight MAX and MIN value based on month”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

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. Press with left mouse button on the "Conditional formatting" button

4. Press with left mouse button on "New rule..."

5. Press with left mouse button on "Use formula to determine which cells to format"

6. Type formula

7. Press with left mouse button on "Format..." button

8. Select a color.

9. Press with left mouse button on 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:

Get the 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,""))

What forumula is required to record the highest value during live data in the Excel seat

I find this is a very helpful page for my research for time series analysis.