How to Find Range in Excel: MAX-MIN, Formulas, and Statistical Methods

If you have ever scrolled through a column of numbers in a spreadsheet and wondered how spread out they really are, you are asking the right question. The range is the simplest summary of variability that statistics gives us. It tells you the gap between the largest and smallest value, and Excel hands you everything you need to calculate it in seconds.

The trick is knowing which formula fits your situation, because finding the range in Excel is not always as straightforward as subtracting one cell from another, especially when your data has blanks, errors, hidden rows, or conditions you only want to count under certain circumstances.

This guide walks through every reliable method for calculating range in Excel. We start with the textbook MAX minus MIN approach that most teachers expect, then move into conditional ranges, named ranges (which is a different concept that confuses many beginners), filtered ranges, and even array formulas for dynamic datasets.

What "Range" Means in Excel (Two Definitions)

Before you type a single formula, sort out which range you actually need. Excel uses the word "range" in two very different ways, and confusing them causes hours of frustration.

The first meaning is the statistical range: the difference between the highest value and the lowest value in a dataset. If your scores are 45, 72, 88, 91, and 60, the range is 91 minus 45, which equals 46. That number describes how spread out your data is. A small range suggests consistency; a large range suggests variability.

The second meaning is a cell range, written as something like A1:A20 or B5:F25. This is just a rectangular block of cells you are referring to in a formula. When someone asks how to "find the range" of cells they have selected, they usually want the address in the Name Box (the small box left of the formula bar). That is not a calculation. It is just an address.

For the rest of this guide, "range" means the statistical kind, the spread between high and low. We will cover named ranges and cell-range addresses in their own sections so you can spot the difference quickly.

The Basic Formula: MAX Minus MIN

The fastest way to calculate statistical range in Excel is to subtract the minimum value from the maximum value. There is no built-in RANGE function, but the two pieces are so simple that nobody misses it.

Say your data sits in cells A2 through A21. In any empty cell, type this formula:

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

Press Enter. Excel scans the range, finds the largest value, finds the smallest value, and returns the difference. Done. This is the formula you will use 90 percent of the time, and it works whether you have ten values or ten thousand.

A practical tip: highlight your data first and check the bottom-right of the Excel window. The status bar already shows Sum, Average, Count, Min, and Max by default. You can do the subtraction in your head for a quick sanity check before you commit to a formula.

Right-click the status bar to add or remove these mini-readouts. Many users do not realize they can customize what appears there, so they miss free real-time summaries that are always one glance away.

Step-by-Step: Finding Range with Sample Data

Let us walk through a concrete example. Open a blank workbook. In column A starting at A2, paste these test scores: 78, 92, 65, 88, 71, 95, 60, 83, 77, 90. In cell C2, type a label like "Range" and in D2 enter the formula:

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

You should see 35 appear in D2. Maximum is 95, minimum is 60, difference is 35. That is your range. Now here is where most tutorials stop, but in real data you almost never get a clean column.

You get blanks, text labels that wandered in by mistake, error values from broken lookups, and numbers that need to be filtered. Each of those situations needs a slightly different formula. Keep reading.

Handling Blank Cells and Text in Your Data

MAX and MIN ignore blank cells and text automatically. That sounds great, but it can hide problems. If column A has a stray text entry like "N/A" in the middle, MAX still returns the largest number. It does not warn you. Your range calculation looks correct even though your data is dirty.

To verify your dataset is clean before trusting the range, use COUNT to count numeric values and COUNTA to count all non-blank cells. If those two numbers differ, you have non-numeric entries:

=COUNTA(A2:A21)-COUNT(A2:A21)

A nonzero result means you have text mixed in. Filter the column or use find and replace in Excel to clean those entries before calculating range.

Error values are different. If even one cell in your range contains #N/A or #DIV/0!, MAX and MIN will return that error, and your subtraction will too. Wrap the formula in IFERROR or use AGGREGATE (covered below) to skip errors gracefully.

Using AGGREGATE for Errors and Hidden Rows

The AGGREGATE function is a Swiss Army knife most spreadsheet users never learn. It lets MAX and MIN ignore errors and even ignore hidden rows from filters. The syntax has two important arguments at the front: the function number and the options number.

For maximum, the function number is 4. For minimum, it is 5. The options 6 ignores error values, 5 ignores hidden rows, and 7 does both. So a range formula that survives errors and works with filtered data looks like this:

=AGGREGATE(4,7,A2:A1000)-AGGREGATE(5,7,A2:A1000)

This is the formula to use in any dashboard or report where data quality is uncertain. It returns a clean number even when half your column has broken cells.

Conditional Range with MAXIFS and MINIFS

What if you only want the range for a subset of rows? For example, you have sales data and you only want the range of revenue for the Western region. MAX and MIN cannot filter by themselves. You need MAXIFS and MINIFS, which were added in Excel 2019 and are standard in Microsoft 365.

The syntax is simple. Pass the value range first, then the criteria range, then the criteria. To get the range of values in column B where column A equals "West":

=MAXIFS(B2:B1000,A2:A1000,"West")-MINIFS(B2:B1000,A2:A1000,"West")

You can add more criteria pairs to narrow further. This is invaluable when comparing groups: salary range by department, test score range by class, temperature range by city. If you are still on an older version of Excel without MAXIFS, you can use array formulas with MAX(IF(...)) instead, though the modern functions are much cleaner.

Named Ranges (A Different Concept)

The phrase "named range" comes up so often that people conflate it with the statistical range we have been calculating. They are unrelated. A named range is simply a label you attach to a cell or block of cells so your formulas read in plain English instead of cryptic addresses.

To create a named range, select the cells you want to name, click in the Name Box (left of the formula bar), type a name like "Scores" (no spaces, no starting with numbers), and press Enter. From that point forward you can write formulas like:

=MAX(Scores)-MIN(Scores)

This calculates the same range as before, but readers of the formula know immediately what data you are summarizing. Named ranges shine in long workbooks where you reference the same data across dozens of sheets.

The Name Manager (Formulas tab, Name Manager button) lets you see every named range in your workbook, edit them, or delete duplicates. Audit it occasionally because broken named ranges are a classic source of mysterious errors.

Finding the Range in Filtered Data Only

Filters hide rows but do not delete them. MAX and MIN will happily include hidden rows in their calculation, which is almost never what you want when you are looking at a filtered table. Two functions handle filters correctly: SUBTOTAL and AGGREGATE.

SUBTOTAL is older and simpler. Function 104 means MAX ignoring hidden rows; function 105 means MIN ignoring hidden rows. A filtered-aware range formula:

=SUBTOTAL(104,A2:A1000)-SUBTOTAL(105,A2:A1000)

Apply any filter to your data, and watch the range update automatically as you narrow the visible rows. This is one of the most useful tricks for ad-hoc analysis: filter to one customer, see their score range, switch filter, see the next one. No formula edits required.

Dynamic Range with Tables and Structured References

If your data grows over time (new rows added every week, for instance), hard-coded ranges like A2:A1000 become a maintenance headache. Convert your data to an Excel Table (Insert > Table or Ctrl+T) and reference the column by name. Suppose your table is called SalesData and the column is "Revenue":

=MAX(SalesData[Revenue])-MIN(SalesData[Revenue])

The formula automatically expands as you add rows to the table. No more guessing how many rows to include. Tables also make totaling a column trivial, because you just turn on the Total Row checkbox and pick the function.

This approach pairs well with PivotTables. You can pull the same SalesData into a PivotTable and use the Field Settings to display Max and Min side by side, then add a calculated field for the difference. That gives you ranges by category without writing a single formula.

Range vs. Other Measures of Spread

Range is the easiest measure of dispersion but also the crudest. It is sensitive to outliers: one extreme value can blow up the range and make your data look more spread out than it really is. For a more robust summary, statisticians use interquartile range, variance, or standard deviation.

In Excel, those are also one-line formulas. Interquartile range is QUARTILE(data,3) minus QUARTILE(data,1). Standard deviation is STDEV.S(data) for a sample or STDEV.P(data) for a full population. Variance is VAR.S or VAR.P.

That said, range remains the right metric in plenty of cases: temperature swings during a day, price spread in a market, score gaps in a class. When the question is literally "how wide is the gap," range is what you want, and the MAX-MIN formula is all you need.

Common Mistakes to Avoid

Three mistakes show up over and over when people calculate range in Excel. First, selecting the wrong cells. Double-check that your MAX and MIN reference the exact same range. Typing MAX(A2:A21) and MIN(A2:A20) accidentally chops one cell off the bottom and silently changes the result.

Second, including header rows. If A1 says "Scores" and your formula reads MAX(A1:A21), Excel handles the text gracefully but the range looks odd in tooltips and audits. Start at A2 if A1 is a header.

Third, forgetting to refresh after adding data. If you wrote MAX(A2:A100) and then added 50 more rows below row 100, your range stops updating. Either expand the formula range, switch to a Table, or use an entire column reference like MAX(A:A).

Visualizing the Range on a Chart

Numbers in a cell tell part of the story. A chart tells the rest. If you want to communicate range visually, two chart types work especially well in Excel: the high-low-close stock chart and the box plot.

The high-low-close chart is technically a financial chart, but it works for any dataset where you have a maximum and minimum per category. Lay out your data with categories in one column and the high and low values in adjacent columns. Insert > Charts > Stock > High-Low-Close. Excel draws a vertical bar from min to max for each category.

Box plots (also called box-and-whisker charts) go further. They show not just the range but also the quartiles, so viewers can see whether data clusters near the middle or spreads evenly. Excel 2016 and later have a built-in box plot under Insert > Charts > All Charts > Box and Whisker.

Calculating Range Across Multiple Sheets

If your data is split across several worksheets (one tab per month, for example), you can still calculate a single range across all of them using 3D references. The syntax bridges sheet names with a colon. If you have sheets named Jan, Feb, Mar, Apr and the data sits in B2:B100 on each sheet:

=MAX(Jan:Apr!B2:B100)-MIN(Jan:Apr!B2:B100)

Excel walks through every sheet between Jan and Apr (inclusive) and treats them as one combined dataset. If you insert a new sheet between Jan and Apr later, it gets pulled into the calculation automatically. This is enormously powerful for monthly or quarterly reporting.

One gotcha: 3D references work for MAX and MIN but not for MAXIFS or MINIFS. If you need conditional range across sheets, consolidate your data into a single sheet first (Power Query is great for this) or use a PivotTable with multiple consolidation ranges.

Practice Makes Perfect

Reading about formulas is one thing; using them on real data is another. Build a small practice file with 30 to 50 random numbers and try each technique we covered: basic MAX-MIN, AGGREGATE for errors, SUBTOTAL for filters, MAXIFS for conditions, and structured references inside a Table.

Time yourself. After three or four passes, you will reach for the right formula on instinct, and your future spreadsheets will be easier to audit and explain to others. And if you want to sharpen the rest of your toolkit, work through how to use Excel next.

It rounds out the everyday spreadsheet skills nicely. Bookmark this page so you have the conditional and filtered formulas handy the next time someone asks for the spread in a column. The MAX-MIN combo will cover most cases, but the moment you hit dirty data or filters, switch to AGGREGATE or SUBTOTAL and your output stays trustworthy without extra cleanup work.

One last note for analysts: when you present a range in a report, always include the count of values that went into it. A range of 100 across three values is a very different story from a range of 100 across three thousand values. Context matters, and your audience will trust your numbers more when the supporting detail is right there next to them.