Find Range in Excel (Table of Contents)
Range in Excel
Whenever we talk about the range in excel, it can be one cell or can be a collection of cells. It can be the adjacent cells or non-adjacent cells in the dataset.
What is Range in Excel & its Formula?
A range is the collection of values spread between the Maximum value and the Minimum value. In mathematical terms, a range is a difference between the Largest (maximum) value and the Shortest (minimum) value in a given dataset.
Range defines the spread of values in any dataset. It calculates by a simple formula like below:
Range = Maximum Value – Minimum Value
How to Find Range in Excel?
To find a range is a very simple process and it is calculated by using the Excel in-built functions MAX and MIN. Let’s understand the working of finding a range in excel with some examples.
Range in Excel – Example #1
We have given below a list of values:
23, 11, 45, 21, 2, 60, 10, 35
The largest number in the above-given range is 60 and the smallest number is 2.
Thus, the Range = 60-2 = 58
- In this above example, the Range is 58 in the given dataset which defines the span of the dataset. It gave you the visual indication of the range as we are looking for the highest and smallest point.
- If the dataset is large, it gives you the widespread of the result.
- If the dataset is small, it gives you the closely centered result.
A process of defining the range in Excel
For defining the range, we need to find out the maximum and minimum values of the dataset. In this process, two function plays a very important role. They are:
Use of MAX function:
Let’s take an example to understand the usage of this function.
Range in Excel – Example #2
We have given some set of values:
For finding the maximum value from the dataset, we will apply here the MAX function as below screenshot:
Hit enter and it will give you the maximum value. The result is shown below:
Range in Excel – Example #3
Use of MIN function:
Let’s take the same above dataset to understand the usage of this function.
For calculating the minimum value from the given dataset, we will apply MIN function here as per below screenshot.
Press ENTER key and it will give you the minimum value. The result is given below:
Now you can find out the range of the dataset after taking the difference between Maximum and Minimum value.
We can reduce the steps for calculating the range of a dataset by using the MAX and MIN functions together in one line.
For this, we again take an example to understand the process.
Range in Excel – Example #4
Let’s assume the below dataset of a company employee with their achieved sales target.
Now for identifying the span of sales amount in the above dataset, we will calculate the range. For this, we will follow the same procedure as we did in the above examples.
We will apply the MAX and MIN functions for calculating the Maximum and Minimum sales amount in the data.
For finding the range of sales amount, we will apply the below formula:
Range = Maximum Value – Minimum Value
Refer below screenshot:
Press Enter key and it will give you the range of the dataset. The result is shown below:
As we can see in the above screenshot, we applied the MAX and MIN formulas in one line and by calculating their results difference, we found the range of the dataset.
Things to Remember
- If the values are available in the non-adjacent cells for which you want to find out the range, then you can pass the cell address individually, separated with comma as an argument of the MAX and MIN functions.
- We can reduce the steps for calculating the range by applying MAX and MIN functions in one line. (Refer Example 4 for your reference)
This has been a guide to Range in Excel. Here we discuss how to find Range in Excel along with excel examples and downloadable excel template. You may learn more about Excel from the following articles –