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:

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:

1. Click an empty cell where you want the result
2. Type =MAX(
3. Select your data range (or type it manually, e.g., A1:A20)
4. Type ) - MIN(
5. Select the same data range
6. 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:

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

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

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:

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

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:

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.