How to calculate the range of a data set in Excel
Formula (Excel and Google Sheets)
=MAX(B2:B8)-MIN(B2:B8)Subtract the smallest value from the largest: =MAX(B2:B8)-MIN(B2:B8).
- Result on the example
- 22 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
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.
=MAX(B2:B8)-MIN(B2:B8)In Google Sheets
The same formula works in Google Sheets: =MAX(B2:B8)-MIN(B2:B8). We ran it in Google Sheets mode on the same data and it returns 22.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =LARGE(B2:B8,1)-SMALL(B2:B8,1) | 22 | 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.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate the range of a data set
E1, calculate the range of the high temperatures: the highest minus the lowest.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").
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.