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

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 date ranges overlapping selected record [VBA]

The following example shows you how to highlight overlapping ranges. How it works Select a date in the table. Conditional […]

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Highlight smallest duplicate number

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

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Highlight dates in a date range

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

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the […]

Question: I am trying to create an excel spreadsheet that has a date range.Example: Cell A1 1/4/2009-1/10/2009 Cell B1 1/11/2009-1/17/2009 […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Combine data from multiple sheets

Question: Problem description (simplified of course): I have a list of employees (by ID number) and date (by yr & […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

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 […]

List all unique events in a month

Question: I have a table with four columns, Date, Name, Level, and outcome. The range is from row 3 to […]

### 7 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

Use the img tag, like this: <img src="Insert pic link here">

**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. 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,""))