How to Find Range in Excel

If you need to find the range of a data set in Microsoft Excel, there are many ways to go about it. It could be as easy as one simple calculation on a sorted data set or as complex as inserting a multi-part conditional formula to eliminate outliers and anomalies.

This guide will give you the solid grounding you need to find the best way to calculate the range in your spreadsheets.

Useful Range Functions in Excel

Though not exhaustive, this is a list of the most common functions relating to range in Microsoft Excel that you should be aware of.

• MAX: This returns the maximum value in a range. The function reads =MAX(A1:A10).
• MIN: This returns the minimum value in a range. The function reads =MIN(A1:A10).
• LARGE: This returns the largest value in a range. At a glance, it might seem identical to MAX, but LARGE can also calculate the nth largest value in a range. The function reads =LARGE(A1:A10,k), where k is the position from the largest value.
• SMALL: This returns the smallest value in a range. Again, it differs from MIN in calculating the nth smallest value. The function reads =SMALL(A1:A10,k), where k is the position from the smallest value.
• COUNTA: This is your function if you look at how many values are in your range. It works for both number and text. The function reads =COUNTA(A1:A10).
• COUNTIF: This function finds values in a range that meet certain criteria. If, for example, you need to find how many times a certain value appears, you input that value into the criteria. The function reads =COUNTIF(A1:A10,k), where k is the criteria.
• RANK: This returns the ranking of a certain value in a range, either ascending or descending. By default, one is the highest rank. The function reads =RANK(k;A1:A10), where k is the value that needs to be ranked.
• MEDIAN: This returns the median average value in a range. The function reads =MEDIAN(A1:A10).
• IF: This conditional function enables you to set your own parameters for data. We will examine this function in more detail later. The most basic function reads =IF(A1:A10,”low”,”high”).

How to Input Functions

Excel’s functions are special formulae typed in the command ribbon to perform complex tasks quickly. They all begin with the = sign. These functions use specific cells in your spreadsheet to calculate Cell References (A5, B10, etc.). What makes Excel so flexible and efficient is that no matter how many times the data in these cells may change, the function will still calculate.

If you’re comfortable with functions, you can manually input them within the Formula Bar. If you are unsure about the format of the function or need a walkthrough, click on the Insert Formula button next to the Formula Bar (ƒ) and search for your desired function, or type a brief description of what you’re trying to do.

How to Calculate Range in Excel

The range is the difference between the highest and lowest number in a data set. Range is the number you get by subtracting the lowest number in the set from the highest. If the data ranges from 34-98, then the range of that data will be 64. This holds true for all numbers, from sports scores to money.

Now that you have the fundamentals, let’s look at various ways on how to find the range in Excel for different tasks and parameters:

The Basic MIN MAX Calculation

Say you have a random, unsorted data set and must find the minimum and maximum values to calculate the range. Here’s what to do:

1. Select a cell to insert the function.
2. Insert the formula; it should read =MAX(B2:B10)-MIN(B2:B10).
   

You can also split the MIN and MAX functions into different cells if having both values is important. For example, B1 is your maximum value, and B2 is your minimum. To calculate the range in B3, simply type =B1-B2.

The Basic SMALL LARGE Calculation

These functions can find the nth ranking of any value in your set but can also be used like the MIN and MAX functions above. To find the range, do the following:

1. Select a cell to insert the function.
2. Input the formula using LARGE and SMALL. It should look like this =LARGE(C2:C10, “1”) -SMALL(C2:C10, “1”) to calculate the difference between the largest and smallest numbers in the range C2:C10.
   

The Advanced SMALL LARGE Calculation

Say you want to find your set’s highest three and lowest three values. Perhaps you have outliers or anomalies you want to exclude from the range. The formula then gets a little more complex. To find these values, do the following:

1. In three separate cells, insert the following function =LARGE(A1:A10,k), changing k to values 1,2 and 3 for each cell. These are your three largest values.
   
2. Do the same in another three cells using =SMALL(A1:A10,k), to display your smallest three values.
   
3. You can then use any of these values to calculate a range. Just subtract a small value from a large one.
   

The Conditional Calculation

Sometimes, you may need to find a range in Excel based on certain conditions or parameters. The IF function runs through the selected value set, testing each cell for the truth value in your formula, e.g. (>2000), returning a True or False value accordingly.

In recent years, IF has become integrated into other functions to make it easier to define your conditions, e.g., SUMIF, COUNTIF. There are many ways to set up an IF function to calculate a range, but let’s focus on one example:

Say you have a value set that mostly has numbers between 100 and 1000. Some outliers are above and below this range, but you wish to exclude these values to produce more reliable statistics.

Microsoft 365 and Excel 2019 or newer:

1. Select the cell for the function.
2. Type =MAXIFS, then input your maximum range, criteria range, and criteria. It will look like this to return a value more than 79: =MAXIFS(C2:C10,C2:C10, “>78”).
3. In a different cell, type =MINIFS, then input your minimum range, criteria range, and criteria. It will look like this: =MINIFS(C2:C10,C2:C10,”>69″)
   
4. Finally, subtract both values.
5. Alternately, in a single cell, you can combine the formula. =MAXIFS(C2:C10,C2:C10, “>78”)– MINIFS(C2:C10,C2:C10,”>69″).

Excel Versions Before 2019

If you have an older version of Excel, you need to place the IF function within the MAX and MIN functions. The formula will look like this: =MAX(IF(C2:C10,“>78”,C2:C10, ))- MIN(IF(C2:C10,“>69“,C2:C10)).

Many Ways to Find the Range

There are several ways to find the range in an Excel spreadsheet. The best tip when using any of the range functions is not to overcomplicate your calculations. If the simplest function gets it done, go for that rather than combining several lines of code and conditionals.

If you prefer using Google Sheets to calculate range, the process is similar, with minor formula changes.

FAQs

What is data range in Excel?

This may be a set of cells, rows, or columns in Excel. Sometimes, it may also refer to the difference between a selection’s highest and lowest values.

Why do we calculate range?

Range denotes how the central tendency reflects the data; it is the disparity between the greatest and smallest number in the data set. The central tendency is less indicative of the data if the range is wide than it is if it is narrow.