How to Find Range in Excel (Easy Formula)

When working with datasets in Excel, sometimes we need to perform some basic statistical analysis on the data. We might want to look at the mean value, max value and so on. One value that is often valuable and easy to calculate in Excel is the range.

In this simple guide, we are going to run through how to calculate the range of the highest and lowest values in a dataset in Excel. We will cover:

• What is Range?
• Finding Range with a formula
• Finding Range with descriptive statistics

Let’s get started with a quick definition.

What is Range?

Technically range can mean a few different things – such as how much of a variety is there in values, or the spread of data in the spreadsheet itself (eg data from cells A1 down to A10) – for the purposes of this tutorial, we are simply referring to the overall range as a mathematical concept which is the difference in value between the highest and lowest figures in our dataset that we are looking at.

For example in the following set of values – 3, 5, 1, 10, 4, 5 – the lowest value is 1 and the highest value is 10 – therefore the range (10-1) is 9.

In this guide we are going to use the below dataset of student exam scores:

As we can see, the scores vary quite a bit for different students, and the values aren’t sorted highest to lowest so it can be hard to intuitively see what the range here would be.

The first approach we are going to look at to find range is a formula.

Finding the range of values using a formula

The formula we are going to use is technically three formulas in one as we are going to use a combination of MAX, MIN, and Subtract.

MAX is a formula where you select a range of cells and it outputs the highest value, while MIN does the opposite – outputs the lowest value.

The syntax for our combined formula will therefore be as follows:

=MAX(Cells) – MIN(Cells)

In the context of the above dataset, it would look as follows:

=MAX(C3:C22)-MIN(C3:C22)

The overall output of this formula is below, along with the results of the MAX and MIN formulas as well:

As we can see, the overall range here is 79. Looking at the individual outputs of 98 and 19 as well we can see that this is correct.

If all we want is to find the range, then this formula more than does the job and is quite easy. However we do have another option that gives us the same result plus more – descriptive statistics.

Finding the range of values using descriptive statistics

Excel has a really handy built in feature called descriptive statistics which when provided with a range of values outputs a series of values including range.

We have a separate guide here on how to set up and run the descriptive statistics function so won’t go into a whole lot of detail in this guide. That being said, if we were to run the function the output on a new sheet would be as follows:

As we can see, outside of outputting the exact same figures of 19, 98 and 79 that we saw above for MAX/MIN/Range, we also automatically are presented with some of the other values mentioned at the start of this article such as mean, median, standard deviation and so on.

This function can be so handy as generally when working with a dataset such as the sample set here, there are often quite a few different things we want to measure – so this saves us quite a bit of time in setting up formulas one by one.

This sums up our simple guide on how to find range in Excel. For more handy guides on working with Excel, be sure to check out our Excel Tips page.