How to Calculate Range in Excel: Formulas, Methods, and When Each Applies

Range is one of the most fundamental statistical concepts in data analysis — and Excel gives you several ways to calculate it depending on what your data looks like and what you're trying to find. Whether you're working with a clean list of numbers or a dataset full of gaps and conditions, understanding which approach fits your situation makes a real difference.

What "Range" Actually Means in This Context

In statistics, range is the difference between the highest and lowest values in a dataset. It's a quick measure of spread — how wide your data is from one extreme to the other.

Formula: Range = Maximum Value − Minimum Value

Excel doesn't have a single built-in RANGE() function for this purpose (the word "range" in Excel also refers to a group of cells, which is a separate concept). Instead, you combine two functions: MAX() and MIN().

The Basic Range Formula in Excel

For a straightforward dataset with no empty cells or special conditions:

=MAX(A1:A20) - MIN(A1:A20)

This tells Excel to find the largest value in cells A1 through A20, subtract the smallest value, and return the result. That's your range.

Step-by-step:

Click an empty cell where you want the result
Type =MAX(
Select your data range (or type it manually, e.g., A1:A20)
Type ) - MIN(
Select the same data range
Close with ) and press Enter

This works for numbers, dates, and times — Excel treats all of these as numeric values internally.

When Your Data Includes Blank Cells or Text

MAX() and MIN() naturally ignore blank cells and text entries, so a basic gap in your data won't break the formula. However, there are situations where this default behavior becomes a variable worth understanding:

Zeros vs. blanks: If a blank cell represents "no data," MIN() will still skip it. But if a cell contains 0 as a legitimate value, it will be included — and could become your minimum, which may or may not reflect what you want.
Text that looks like numbers: If numbers are stored as text (a common import issue), MAX() and MIN() will skip them entirely, silently giving you a wrong result.

To check whether numbers are stored as text, look for a small green triangle in the corner of the cell, or use =ISNUMBER(A1) to test a cell.

Calculating Range With Conditions 🎯

Sometimes you only want the range for a specific subset of data — for example, the range of sales figures for one product category, or scores above a certain threshold.

Excel doesn't have MAXIF/MINIF functions in older versions, but it does have MAXIFS and MINIFS, available in Excel 2019, Excel 365, and Excel for Mac 2019 onward.

Syntax:

=MAXIFS(max_range, criteria_range, criteria) - MINIFS(min_range, criteria_range, criteria)

Example — finding the range of sales only for "Region: North":

=MAXIFS(B2:B100, A2:A100, "North") - MINIFS(B2:B100, A2:A100, "North")

For older Excel versions without MAXIFS/MINIFS, you can use array formulas:

=MAX(IF(A2:A100="North", B2:B100)) - MIN(IF(A2:A100="North", B2:B100))

In older Excel, this must be entered with Ctrl + Shift + Enter instead of just Enter, which wraps it in curly braces {} to signal an array formula. In Excel 365, you can press Enter normally.

Calculating Range Across Multiple Columns or Non-Contiguous Cells

MAX() and MIN() can accept multiple arguments, including non-adjacent ranges:

=MAX(A1:A20, C1:C20, E1:E20) - MIN(A1:A20, C1:C20, E1:E20)

This calculates the overall range across all three columns at once — useful when your data isn't laid out in a single continuous block.

A Quick Reference: Which Formula Fits Which Situation

Situation	Formula Approach
Simple numeric list, no conditions	`=MAX(range) - MIN(range)`
Data with intentional zeros	Same — confirm zeros are valid values
Conditional range (modern Excel)	`=MAXIFS(...) - MINIFS(...)`
Conditional range (older Excel)	Array formula with `MAX(IF(...))`
Non-contiguous columns	`=MAX(range1, range2) - MIN(range1, range2)`
Numbers stored as text	Fix data first, then standard formula

Understanding Range vs. Other Spread Measures

Range is fast to calculate and easy to interpret, but it has a known limitation: it only reflects the two most extreme values. A single outlier can make a dataset look far more spread out than it actually is.

If you find yourself questioning whether the range is giving a useful picture of your data, that's a signal that other statistical measures — like standard deviation (STDEV), interquartile range (calculated using QUARTILE or PERCENTILE), or variance (VAR) — might tell a more complete story.

The interquartile range (IQR), for instance, focuses on the middle 50% of values and ignores outliers entirely:

=QUARTILE(A1:A20, 3) - QUARTILE(A1:A20, 1)

The Variables That Shift the Right Approach 📊

Which formula actually works for your data depends on factors specific to your spreadsheet:

Excel version — MAXIFS/MINIFS aren't available in Excel 2016 or earlier
Data structure — continuous range, multiple columns, or filtered table
Data quality — whether blanks, zeros, and text entries mean what you think they mean
What you're measuring — overall spread vs. conditional spread vs. outlier-resistant spread

A dataset of 20 clean test scores and a dataset of 10,000 mixed-format sales records will call for meaningfully different approaches — even though the underlying concept is the same subtraction.