Range and Interquartile Range (IQR)

Excel Formulas › Statistics

All versionsQUARTILE

Two quick spread measures: the range (max minus min) shows the full extent, and the IQR (the middle 50%) shows the typical spread, ignoring outliers.

Quick formula: range and IQR of B2:B100:
=MAX(B2:B100)-MIN(B2:B100) // range =QUARTILE(B2:B100,3)-QUARTILE(B2:B100,1) // IQR
Range uses the extremes; IQR is Q3 minus Q1, the spread of the middle half.

Functions used (tap for the full reference guide):

QUARTILEMAX

The example

Spread shown two ways.

AB
1MeasureValue
2Range60
3IQR (Q3−Q1)22

The formula

The formula:

=QUARTILE(rng,3) - QUARTILE(rng,1) // middle-50% spread

How it works

How it works:

  1. Range = MAX − MIN — the total spread, but sensitive to a single outlier.
  2. IQR = QUARTILE(rng,3) − QUARTILE(rng,1) — the spread of the middle 50%, which ignores extremes.
  3. A large range but small IQR signals outliers stretching the extremes.
  4. IQR powers the standard outlier rule: anything beyond Q1 − 1.5×IQR or Q3 + 1.5×IQR.

Robust vs sensitive: the IQR is resistant to outliers (like the median), while the range and standard deviation are not. Report the IQR for skewed data; the range for a quick “how wide.”

Try it: interactive demo

Live demo

Values.

Range · IQR

Variations

Range only

Full extent:

=MAX(rng) - MIN(rng)

Outlier fences

1.5×IQR rule:

=Q1 - 1.5*IQR and Q3 + 1.5*IQR

QUARTILE.INC

Modern name:

=QUARTILE.INC(rng, 1)

Pitfalls & errors

Range is fragile. One extreme value blows up the range; the IQR resists that.

QUARTILE vs .INC/.EXC. Classic QUARTILE = inclusive; .EXC excludes the endpoints and gives slightly different Q1/Q3.

Small samples. Quartiles are unstable with very few values.

Practice workbook

📊
Download the free Range and Interquartile Range (IQR) practice workbook
A range/IQR sheet with the range, outlier-fence, and .INC variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate the range and IQR in Excel?
Range = MAX(range)-MIN(range). IQR = QUARTILE(range,3)-QUARTILE(range,1), the spread of the middle 50%.
Why use IQR instead of range?
The IQR ignores the extremes, so it's resistant to outliers. The range can be inflated by a single unusual value.
How do I find outliers with the IQR?
Flag values below Q1 - 1.5×IQR or above Q3 + 1.5×IQR.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Percentile & quartile · Find outliers · Standard deviation

Function references: QUARTILE · MAX