Excel library

How to Find the Range in Excel (3 Easy Formulas)

By · Formulae

How to Find the Range in Excel featured image showing the MAX minus MIN formula returning 4800

You have a column of sales figures and you want to know how far apart your best and worst results are. That gap is the range: the highest value minus the lowest value. In this tutorial, I'll show you how to find the range in Excel with three formulas: one for a plain list, one for filtered rows and one for each group in your data.

Quick answer
  1. Click an empty cell, such as E2.
  2. Type =MAX(C2:C7)-MIN(C2:C7), using your own cell range.
  3. Press Enter. The cell shows the range.

For filtered rows use Method 2, and for a separate range per group use Method 3.

What Is Range in Excel?

In statistics, the range tells you how spread out your numbers are. You calculate it as highest value - lowest value. Excel does not have a built-in RANGE function for this, so you build it from two functions you probably already know.

This article is about the statistical range. If you meant a cell range such as A1:C7, see how to create a dynamic named range in Excel instead.

All three methods use the same table of monthly sales. Month is in column A, Region in column B and Sales in column C, with the data in A1:C7.

Method 1: Using the MAX and MIN Functions

Use this method when you want the range of every number in a list. It works in every version of Excel.

  1. Select cell E2, where you want the range to appear.
  2. Type the formula below.
  3. Press Enter.
fxFormula
=MAX(C2:C7)-MIN(C2:C7)
Sales data in C2:C7 with the range formula in E2 returning 4800
The formula in E2 returns a range of 4800

How This Formula Works

MAX(C2:C7) returns the largest sales figure, which is 9100. MIN(C2:C7) returns the smallest, which is 4300. Excel subtracts the second from the first: 9100 - 4300 = 4800.

Note: MAX and MIN ignore empty cells, text and TRUE or FALSE values in the range. If any cell in the range holds an error such as #N/A, the formula returns that error too.

Show the Highest, Lowest and Range Separately

If you want to see the two values behind the range, put each in its own cell. Enter =MAX(C2:C7) in F1 and =MIN(C2:C7) in F2. Then enter the formula below in F3.

fxFormula
=F1-F2
Highest, lowest and range shown in separate cells F1 to F3
The highest value in F1 and the lowest in F2 give a range of 4800 in F3

This takes more cells, but readers can check each number at a glance. It is a good layout for a report.

Method 2: Using SUBTOTAL to Find the Range of Filtered Rows

MAX and MIN include rows you have hidden with a filter. Use SUBTOTAL when you want the range of only the rows you can see.

  1. Select any cell in your table, then press Ctrl + Shift + L on Windows, or go to Data > Filter.
  2. Click the drop-down arrow in the Region header.
  3. Clear the South box so only North stays ticked, then click OK.
  4. Select cell E2 and type the formula below.
  5. Press Enter.
fxFormula
=SUBTOTAL(104,C2:C7)-SUBTOTAL(105,C2:C7)
Sales table filtered to the North region with the SUBTOTAL range formula in E2 returning 1800
With the South rows filtered out, the formula returns a range of 1800

How This Formula Works

The number 104 tells SUBTOTAL to find the maximum, and 105 tells it to find the minimum. Both ignore rows hidden by a filter. The visible North sales are 5200, 6100 and 4300, so the range is 6100 - 4300 = 1800. Change the filter and the result updates on its own.

Note: Put the formula in a row the filter cannot hide, such as above the table. A filter hides entire rows, so if the formula sits in a row that gets filtered out, you will not see the result.

Method 3: Using MAXIFS and MINIFS to Find the Range by Group

Use this method when you want a separate range for each category, such as each region. MAXIFS and MINIFS are available in Excel 2019, Excel 2021 and Microsoft 365, but not in Excel 2016 or earlier.

  1. In E2 and E3, type the region names North and South.
  2. Select cell F2.
  3. Type the formula below.
  4. Press Enter, then drag the fill handle down to F3.
fxFormula
=MAXIFS($C$2:$C$7,$B$2:$B$7,E2)-MINIFS($C$2:$C$7,$B$2:$B$7,E2)
Range for each region calculated with MAXIFS and MINIFS in F2 and F3
Each region gets its own range: 1800 for North and 1700 for South

How This Formula Works

MAXIFS returns the largest value in C2:C7 where the region in B2:B7 matches the name in E2. MINIFS does the same for the smallest value. For North, the highest is 6100 and the lowest is 4300, so the range is 1800. For South, it is 9100 - 7400 = 1700.

The dollar signs lock the ranges so they stay in place when you fill the formula down. The E2 reference changes to E3 for the South row.

Note: If no row matches the region name, MAXIFS and MINIFS return 0, so the range shows 0. Check the spelling of the name if you see that.

FAQs

Is there a RANGE function in Excel?

No. Excel has no function that returns the statistical range. You subtract the smallest value from the largest with =MAX(range)-MIN(range).

How do I find the range of several columns?

Use one range that covers all the columns, for example =MAX(B2:D7)-MIN(B2:D7). MAX and MIN look at every number in the block.

How do I find the range without the highest and lowest values?

Use LARGE and SMALL to skip the extremes. With the sales data in C2:C7, =LARGE(C2:C7,2)-SMALL(C2:C7,2) returns 3600, which is the second-highest value (8800) minus the second-lowest (5200).

Why does my range formula return 0?

MAX and MIN return 0 when the range has no numbers. Check that your values are stored as numbers and not as text, and that the range points at the right cells.

Can the range be negative?

Not with this formula. The highest value is never smaller than the lowest, so MAX minus MIN is zero or higher.

Conclusion

For most lists, =MAX()-MIN() is all you need. I'd use it first, and switch to SUBTOTAL when you filter your data often, or to MAXIFS and MINIFS when you want one range per category. Once you have the range, you can look at how values are spread with the standard deviation formula or the median formula.

Related Excel Tutorials