Excel Formula for Range of Values: Find the Spread

Formulas & functions · Updated November 18, 2026

To calculate the numerical range of values in Excel, subtract the smallest value from the largest: =MAX(A2:A7)-MIN(A2:A7). If your data is in cells A2 through A7, this returns the spread between the two extremes.

That simple calculation answers the most common meaning of “range of values.” Excel also uses the word range to mean a group of cells, such as A2:A7, so it helps to distinguish the data range from the statistical range you want to calculate.

What range means in Excel

In everyday statistics, the range is the difference between the maximum and minimum values in a set. It is a quick measure of how far apart the extremes are. If the lowest score is 58 and the highest is 92, the range is 34. The range is expressed in the same units as the data: a set of prices produces a range in dollars, while a set of temperatures produces a range in degrees.

In Excel terminology, a cell range is a selection or reference to one or more cells. The colon in A2:A7 means every cell from A2 through A7. It does not mean “calculate the statistical range.” The MAX and MIN functions find the largest and smallest numbers in that cell range; subtracting the results gives the statistical range.

The result describes the distance between the endpoints, not how values are distributed between them. Two lists can have the same range even if one is clustered near the middle and the other is spread across the full interval. That distinction matters when using range to summarize data.

The basic formula and a worked example

With the numbers 58, 64, 71, 83, 92, and 68 in cells A2:A7, enter this formula in an empty cell:

=MAX(A2:A7)-MIN(A2:A7)

MAX returns 92, the largest value, and MIN returns 58, the smallest. The arithmetic is 92 minus 58, so the formula returns 34. The formula updates when the values in A2:A7 change, making it more useful than calculating the difference manually.

You can also put the two parts in separate cells to make the calculation easier to inspect. With the same values in A2:A7, enter =MAX(A2:A7) in B2; it returns 92. With the same layout, enter =MIN(A2:A7) in B3; it returns 58. Then subtract B3 from B2 in another cell. This approach can help when a worksheet needs to show the minimum and maximum alongside the final spread.

For a growing list, consider referencing a larger area that includes likely future entries, such as A2:A100, rather than editing the formula every time a new value is added. Keep in mind that the larger reference should not include unrelated numbers, because any included numeric value can change the minimum or maximum.

How MAX and MIN treat cells

MAX and MIN are convenient because they work directly on a cell reference. In a referenced range, blank cells and text are ignored, while numeric values are considered. A zero is a number, not a blank, so it can become the minimum and increase the calculated range. If your list contains 0, check whether it represents a real measurement or is being used to mean “no data.”

Numbers imported from a text file may look like numbers but be stored as text. MAX and MIN do not treat text in a referenced range as numeric observations. If the result seems wrong, check the source cells: a number stored as text may be left out of the calculation. Converting the entries to numbers can resolve that issue. Also check for error values in the data, since an error in the referenced range can make the calculation return an error rather than a useful spread.

Do not replace missing measurements with zero unless zero is genuinely the measurement. A blank is ignored, but a zero participates in the calculation. This is a common reason a formula reports a range that looks unexpectedly large.

Calculate a range for selected records

Sometimes the list includes multiple groups, and you need the range for just one group. In Microsoft 365 for Windows, MAXIFS and MINIFS can find the extremes that meet a condition. These functions are also available in Excel 2019 and later. For example, suppose column A contains a numeric group code and column B contains measurements. With codes in A2:A7 and values in B2:B7, where code 1 has measurements 58, 71, and 92, use:

=MAXIFS(B2:B7,A2:A7,1)-MINIFS(B2:B7,A2:A7,1)

This returns 34 for group 1: its maximum is 92 and its minimum is 58. The criteria value 1 means that only rows whose code in column A equals 1 are considered. If you use labels rather than numeric codes, MAXIFS and MINIFS can use a text criterion, entered as a quoted text value inside the formula.

Make sure the value range and criteria range cover matching rows. In this example, both ranges start at row 2 and end at row 7. If they are misaligned, Excel may return an error or evaluate the wrong records. If no rows meet a condition, MAXIFS and MINIFS return 0, so a subtraction may display 0 even though there were no matching measurements. Check that the condition actually matches data before interpreting that result.

Ranges for dates and percentages

Excel stores valid dates as serial numbers, so the same MAX-minus-MIN method works for dates. With dates in A2:A7, use =MAX(A2:A7)-MIN(A2:A7) to calculate the elapsed number of days between the earliest and latest date. For example, if the first date is April 1 and the last date is April 7 of the same year, the difference is 6 days. That is elapsed time; if you mean to count both the first and last calendar date, add 1 to the difference.

Format the result as a number if Excel displays it as a date. If the source values include times as well as dates, the difference can include a fraction of a day. For example, half a day is stored as 0.5. Decide whether you want elapsed time in days, hours, or another unit before presenting the result.

A percentage range can mean a different calculation: the absolute spread divided by a chosen baseline. With values 50, 60, 70, and 75 in A2:A5, the formula =(MAX(A2:A5)-MIN(A2:A5))/MIN(A2:A5) returns 0.5, or 50% when the result cell is formatted as a percentage. This compares the spread with the minimum; it is not the same as the ordinary range, which is 25. If the minimum is zero, this percentage calculation divides by zero. With negative values, using the minimum as the baseline may also be misleading, so state clearly how the percentage is defined.

When the full range is not the best summary

The range uses only two observations: the smallest and largest. That makes it easy to understand, but also sensitive to an unusual value. If nearly all measurements are close together and one entry is far away because of a data-entry mistake or an unusual event, the range can overstate the spread of the typical observations. Verify extreme values before deciding whether they should be corrected or excluded; do not remove a valid result merely because it changes the summary.

For a summary that is less driven by the extremes, you might compare the range with the interquartile range, which measures the distance between the first and third quartiles. With the values 10, 20, 30, 40, and 50 in A2:A6, use =QUARTILE.INC(A2:A6,3)-QUARTILE.INC(A2:A6,1). The inclusive quartiles for this example are 40 and 20, so the interquartile range is 20. It answers a different question from MAX minus MIN and may be more informative when extreme observations are present.

For a complete description of a data set, pair the range with measures such as the count, average, or median. The range is most useful as a quick statement of endpoint distance, not as a stand-alone explanation of how every value is distributed.

Common formula problems to check

  • Wrong cells: Confirm that the reference includes all intended observations and does not include a heading, a total, or unrelated figures.
  • Unexpected zero: Look for real zeros, text-formatted numbers, or a conditional formula with no matching records.
  • Unexpectedly large result: Check for a mistaken entry, a different unit, or a zero that represents missing data.
  • Dates displayed oddly: The result may be correct but formatted as a date. Format the result as a number to show elapsed days.
  • Error result: Inspect the referenced cells for errors and confirm the formula references are valid.

For easier maintenance, keep the observations together in one column, use a clear heading, and avoid placing summary figures inside the data range. If you convert the list to an Excel table, adding new rows is easier to manage, although you should still check that your formula refers to the table’s intended data column.

Frequently Asked Questions

What is the Excel formula for the range of values?
Subtract the smallest value from the largest. If the values are in A2:A7, use =MAX(A2:A7)-MIN(A2:A7).

Does Excel have a RANGE function?
No built-in worksheet function named RANGE calculates the statistical spread. Use MAX minus MIN. The word range in Excel usually refers to a group of cells, such as A2:A7.

Does the range formula include zero?
Yes. MAX and MIN consider zero as a numeric value, so zero can become the minimum. Blank cells are ignored. Check whether zeros in your data are actual measurements before interpreting the result.

How do I find the range for one category?
Use MAXIFS and MINIFS with the same criteria range and condition, then subtract the minimum from the maximum. These functions are available in Microsoft 365 and Excel 2019 or later.

How do I calculate the range between two dates?
Subtract the earliest valid date from the latest using MAX minus MIN. Excel returns elapsed days; add 1 if you need to count both endpoint dates as included calendar days.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.