Written and maintained by CASRAI Editorial Board
Last updated
To calculate the range in Excel, subtract the smallest value from the largest: =MAX(A2:A6)-MIN(A2:A6). The range is the largest value minus the smallest value in a data set, and Excel builds it from two built-in functions, MAX and MIN. This guide shows the formula, the AutoSum route for people who prefer menus, the silent ways the result can go wrong, and why a single outlier can dominate it.
What the range is used for in research
The range is the simplest measure of spread. The NIST/SEMATECH e-Handbook of Statistical Methods defines it as the largest value minus the smallest value in a data set, and notes that the measure is based only on the lowest and highest extreme values, so the spread near the center of the data is not captured at all. Because only the two extremes count, a single unusual value can move it a lot. It gives a quick first look at how far a variable spans, and it is easy to calculate and check by hand.
Because it ignores everything between the extremes, the range is usually reported alongside, not instead of, a measure that uses the whole distribution. See the guides on the interquartile range in Excel and on the median and interquartile range for the outlier-resistant alternatives.
Install or access: nothing to add
MAX and MIN are built-in worksheet functions documented by Microsoft Support, so no add-in or Analysis ToolPak is needed. The broader set of functions behind descriptive statistics is covered in the Excel statistics functions guide.
Step-by-step: calculate the range with MAX and MIN
This example uses the sample values from Microsoft’s own MAX and MIN documentation: 10, 7, 9, 27 and 2, typed into cells A2 to A6.
- Enter the values in a single column, one value per cell (A2:A6 here).
- Click an empty cell, for example B2.
- Type
=MAX(A2:A6)-MIN(A2:A6)and press Enter.
Microsoft’s documentation gives =MAX(A2:A6) as 27 and =MIN(A2:A6) as 2 for this data, so the range is 27 − 2 = 25. The syntax is MAX(number1, [number2], …) and MIN(number1, [number2], …), each accepting 1 to 255 arguments, so you can also list several ranges: =MAX(A2:A6,C2:C6)-MIN(A2:A6,C2:C6) gives one range across both columns.
The AutoSum route for people who prefer menus
Microsoft Support describes a menu route for the largest and smallest numbers in a contiguous block. Click an empty cell below or to the right of the numbers, then on the Home tab in the Editing group click the arrow next to Sum, choose Min (or Max), and press Enter. Do this once for each, then subtract the two results in a third cell, for example =B3-B2. For cells that are not next to each other, Microsoft says not to use AutoSum and to type a formula instead (MIN, MAX, LARGE or SMALL).
Calculating the range of several columns
Write the formula once for the first column, then copy it across. With the data for column A in A2:A6, =MAX(A2:A6)-MIN(A2:A6) filled right to column B becomes =MAX(B2:B6)-MIN(B2:B6), so each variable gets its own range.
Output and how to read it
The result is in the same units as the data. A range of 25 for the sample above means the largest value is 25 units above the smallest. Report it with the minimum and maximum themselves (here 2 and 27), since the range alone does not say where on the scale the data sit.
To see how much one value drives the result, use the LARGE and SMALL functions, which Microsoft Support lists as the formula route for cells that are not contiguous. With the sample data, =LARGE(A2:A6,2) returns 10 and =SMALL(A2:A6,2) returns 7. The largest value, 27, is far from the next one, 10: if you set 27 aside, the largest value is 10 and the range falls from 25 to 10 − 2 = 8. That is how the dependence on the two extremes, which NIST points out, plays out in practice, and it is the reason to check the extremes before quoting a range. The guide to the range of a box plot shows how the same extremes appear as whiskers and outlier dots, and the midrange guide covers the other statistic built from MAX and MIN.
Troubleshooting
The range shows 0
Per Microsoft Support, if the arguments contain no numbers, MAX and MIN return 0. A column of numbers stored as text therefore gives 0 − 0 = 0, with no error message. Text, logical values and empty cells in a range or reference are ignored, so a column that is only partly numeric returns a range based on the numeric cells only.
The result is #VALUE! or another error
Microsoft states that arguments that are error values, or text that cannot be translated into numbers, cause errors. An error value in one cell of the range propagates to the formula, so find and fix that cell first. Text typed directly into the argument list, rather than referenced from a cell, causes the error.
Text and TRUE/FALSE you want counted
Logical values and numbers written as text typed directly into the argument list are counted by MAX and MIN. Within a reference they are ignored; Microsoft points to MAXA and MINA for evaluating logical values and text in references.
Free alternatives
| Tool | Formula for the range | What the vendor documents |
|---|---|---|
| Google Sheets | =MAX(A2:A6)-MIN(A2:A6) |
MAX(value1, [value2, …]) and MIN(value1, [value2, …]); text values cause a #VALUE! error, and MAXA or MINA include text. |
The same two-function approach applies; check the stored sheet for text-formatted numbers just as in Excel.
FAQ
How do you calculate the range in Excel?
Type =MAX(range)-MIN(range) in an empty cell, replacing range with your data cells, for example =MAX(A2:A6)-MIN(A2:A6). The result is the largest value minus the smallest value.
How to calculate the range of data in Excel when the cells are not contiguous?
Type the formula and list each block or cell as a separate argument, for example =MAX(A2:A6,C2:C6)-MIN(A2:A6,C2:C6). Microsoft notes that AutoSum does not work for non-contiguous cells.
Does the range ignore blank cells and text?
In a reference, yes: Microsoft Support states that empty cells, logical values and text in a range or reference are ignored by MAX and MIN. If every cell is non-numeric, the functions return 0.
Why is my range so large?
The range depends only on the two extreme values, so one outlier or data-entry error can inflate it. Check the largest and smallest values with MAX, MIN, LARGE and SMALL before reporting it.
What should I report instead of the range?
Because the range uses only the two extremes, it is often useful to report the minimum and maximum together with a measure of spread in the middle of the data, such as the interquartile range. See how to calculate the IQR in Excel.
Sources
- Microsoft Support, MAX function (checked 2026-10-29).
- Microsoft Support, MIN function (checked 2026-10-29).
- Microsoft Support, Calculate the smallest or largest number in a range (checked 2026-10-29).
- NIST/SEMATECH e-Handbook of Statistical Methods, the range (exploratory data analysis section) (checked 2026-10-29).
- Google Docs Editors Help, MAX and MIN (checked 2026-10-29).
