How to Find the Range in Excel: Formulas, Functions, and What Affects Your Results

Understanding range in Excel means two different things depending on context — and knowing which one you're dealing with changes everything about how you approach it.

In statistics, the range is the difference between the highest and lowest values in a dataset. In Excel's interface, a "range" refers to a selected group of cells. Both concepts matter, and Excel handles each in distinct ways.

What "Range" Actually Means in Excel

Before diving into formulas, it helps to separate these two definitions clearly:

• Statistical range — a calculated value: the maximum minus the minimum in a set of numbers
• Cell range — a selection of cells referenced in a formula, like A1:A20

Most people searching for how to find the range in Excel are after the statistical range — that single number that tells you how spread out your data is. That's what this article focuses on.

The Core Formula for Finding Statistical Range 📊

Excel doesn't have a built-in RANGE() function. Instead, you calculate it by combining two functions:

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

This subtracts the smallest value in your dataset from the largest. The result is your range.

Breaking it down:

• MAX() returns the highest value in the specified cells
• MIN() returns the lowest value
• Subtracting one from the other gives you the spread

You can place this formula in any empty cell. Just replace A1:A20 with whatever cell range holds your data.

Step-by-Step Example

Say your data is in column B, rows 2 through 50:

1. Click an empty cell where you want the result
2. Type =MAX(B2:B50)-MIN(B2:B50)
3. Press Enter

Excel instantly returns the difference between the highest and lowest values in that column.

Handling Gaps, Errors, and Edge Cases

Real-world data rarely comes clean. A few situations worth knowing about:

Empty Cells

MAX() and MIN() both ignore empty cells by default, so gaps in your data won't throw off the calculation. You don't need to worry about blank rows within your range.

Text or Error Values

If your range includes cells with text or error values like #N/A or #DIV/0!, the formula may return unexpected results. In those cases, use error-tolerant versions:

=AGGREGATE(4, 6, A1:A20) - AGGREGATE(5, 6, A1:A20) 

The AGGREGATE function can ignore errors and hidden rows — useful when your data is filtered or contains anomalies.

Negative Numbers

The MAX-MIN approach handles negative numbers correctly without any adjustment. If your dataset runs from -50 to 20, the range will correctly return 70.

Using Named Ranges for Cleaner Formulas

If you're working with a table or dataset you reference repeatedly, named ranges make your formulas easier to read and manage.

To create a named range:

1. Select your data cells
2. Click the Name Box (top-left corner, above column A)
3. Type a name like SalesData and press Enter

Your formula then becomes:

=MAX(SalesData) - MIN(SalesData) 

This is particularly useful in larger workbooks where cell addresses change or become hard to track.

Finding Range Across Multiple Columns or Sheets

Multiple Columns

You can extend the range across non-contiguous areas:

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

Both MAX and MIN accept multiple arguments separated by commas.

Across Sheets

To calculate the range across data on different sheets:

=MAX(Sheet1!A1:A20, Sheet2!A1:A20) - MIN(Sheet1!A1:A20, Sheet2!A1:A20) 

This pulls values from multiple sheets into a single calculation. 🗂️

Factors That Change How You Approach This

The right method depends on a few variables that vary by user:

Visualizing Range Alongside Other Statistics ✏️

Range is most useful as part of a broader statistical picture. Excel's Data Analysis ToolPak (available under the Data tab after enabling it in Add-ins) can generate descriptive statistics — including the range — alongside mean, median, standard deviation, and more in a single output table.

This is worth knowing if you're doing exploratory data analysis rather than just pulling a single number.

What the Right Method Looks Like for You

The MAX-MIN formula covers the majority of cases — it's fast, transparent, and requires no special setup. But whether that's sufficient depends on what your data actually looks like: how it's structured, whether it contains errors or filtered rows, which version of Excel you're running, and whether you need the range as a standalone figure or as part of broader analysis.

Each of those variables shifts what "finding the range" actually involves for your specific workbook.