# How to calculate the range of a data set in Excel

Subtract the smallest value from the largest: `=MAX(B2:B8)-MIN(B2:B8)`.

**Excel:** `=MAX(B2:B8)-MIN(B2:B8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Day | High temp (°F) |  | Range |
| Mon | 68 |  |  |
| Tue | 74 |  |  |
| Wed | 59 |  |  |
| Thu | 81 |  |  |
| Fri | 77 |  |  |
| Sat | 63 |  |  |
| Sun | 70 |  |  |

Formula in E1: `=MAX(B2:B8)-MIN(B2:B8)`

Result: 22

## How it works

In statistics, the range is the spread between the extremes: 81 − 59 = 22 degrees. Excel has no RANGE function because "range" already means a block of cells, so MAX minus MIN is the formula.

## Other ways

- `=LARGE(B2:B8,1)-SMALL(B2:B8,1)`: The same with LARGE and SMALL; change the ranks to measure a narrower spread.

## Common mistakes

- One extreme value sets the whole range. For a spread that ignores outliers, use the interquartile range.
- Blank cells are ignored, but zeros count, so a 0 used for "no reading" makes the range far too wide.

## Questions

**How do I calculate the interquartile range?**

Use `=QUARTILE.INC(B2:B8,3)-QUARTILE.INC(B2:B8,1)`.

**How do I calculate the range with a condition?**

Use MAXIFS and MINIFS: `=MAXIFS(B2:B8,A2:A8,"<>Sun")-MINIFS(B2:B8,A2:A8,"<>Sun")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/range-of-a-data-set