Updated August 11, 2023
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. A range is a difference between the Largest (maximum) value and the Shortest (minimum) value in a given dataset in mathematical terms.
Range defines the spread of values in any dataset. It calculates by a simple formula like below:
How to Find Range in Excel?
Finding a range is a very simple process, and it is calculated 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
Explanation:
- In this above example, the Range is 58 in the given dataset, which defines the span of the dataset. It gave you a 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 a 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:
- MAX
- MIN
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 the MIN function here as per the 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 to the 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, you want to find out the range; you can pass the cell address individually, separated with a 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 to Example 4 for your reference)
Recommended Articles
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 –
