# Median, quartiles & outliers (Excel lesson 37 of 45)

> Describe a column of numbers honestly, and flag the values that do not belong.

Source: https://spreadsheetpractice.com/learn/median-quartiles-outliers · Updated October 29, 2026

- **Course:** [Excel course](https://spreadsheetpractice.com/learn), track "Analysis"
- **Time:** about 11 minutes, 4 graded exercises
- **Functions:** `MEDIAN`, `QUARTILE.INC`, `PERCENTILE.INC`, `STDEV`
- **Access:** Pro (the lesson text is free; the graded exercises need Pro)
- **Key formula:** `=MEDIAN(B2:B13)`: The middle value

## How it works

The average is pulled around by extreme values; the **median**, the middle value when sorted, is not. When the two disagree, you probably have outliers.

`QUARTILE.INC(range, 1)` is the value a quarter of the way up the sorted list, and `QUARTILE.INC(range, 3)` is three quarters. The gap between them is the **IQR**, where the middle half of the data sits. `PERCENTILE.INC(range, 0.9)` gives any cut-off you want, such as the 90th percentile.

A common rule flags an **outlier** as anything below `Q1 - 1.5 × IQR` or above `Q3 + 1.5 × IQR`. `STDEV` measures how spread out the values are around the average.

## Syntax

| Formula | What it does |
|---|---|
| `=MEDIAN(B2:B13)` | The middle value |
| `=QUARTILE.INC(B2:B13,1)` | First quartile (Q1) |
| `=PERCENTILE.INC(B2:B13,0.9)` | 90th percentile |
| `=G4-G3` | IQR: Q3 minus Q1 |
| `=IF(OR(B2<$G$6,B2>$G$7),"Outlier","")` | Flag values outside the fences |

## Watch out for

- Quartile numbers are 0 to 4. QUARTILE.INC(range,2) is the median; 1 and 3 are the quartiles.
- Lock the fence cells with $ when you flag a whole column against them.
- An outlier is a prompt to look, not a reason to delete. Check whether it is an error or a real event first.

## The exercises

Solve them in the live grid at [Median, quartiles & outliers](https://spreadsheetpractice.com/learn/median-quartiles-outliers). Each answer is graded, then re-checked on changed data so typed-in numbers don't pass.

1. **Median and quartiles.** These are delivery times in minutes. In G2, find the median. In G3, the first quartile (Q1). In G4, the third quartile (Q3).
2. **IQR and fences.** In G5, calculate the IQR (Q3 minus Q1). In G6, the lower fence: Q1 minus 1.5 times the IQR. In G7, the upper fence: Q3 plus 1.5 times the IQR. Point at the cells above.
3. **Flag the outliers.** In C2, show Outlier when the minutes are below the lower fence or above the upper fence (G6 and G7); otherwise leave it blank (""). Fill down to C13.
4. **Percentile and spread.** In G8, find the 90th percentile of the minutes. In G9, find the standard deviation.

Previous: [Running totals & share of total](https://spreadsheetpractice.com/learn/running-totals-share-of-total) · Next: [MAXIFS & MINIFS by group](https://spreadsheetpractice.com/learn/maxifs-minifs-by-group)

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.
