How to Find Range in Microsoft Excel
Last Updated :
05 Aug, 2024
Understanding the range of a dataset is key to grasping data spread and is essential for interpreting and making data-driven decisions. Excel offers various methods to calculate ranges, enhancing your data analysis skills. This article explains the concept of range in statistics, explores Excel functions for calculating it, and demonstrates practical applications with real-world examples.
How to Find Range in Microsoft Excel
What is the Range?
In Statistics, the range is referred as the difference between the maximum and the minimum value present in the datasets. By finding the range you can sense your datasets in such a way that how much your values of datasets differ from each other. Let's take an example if we have the dataset n = (2,4,5,3,9) then the range of the dataset will be the difference between the maximum value and the minimum value i.e. the range of dataset n will be 9-5 = 4. That's how the range is calculated in the statistics.
In MS Excel there are many functions that can be used with the range of data, some of the functions are as follows:
AVEARGE: The AVERAGE function in MS Excel is used for finding the average within the range of numbers. For example: If we want to calculate the average from a particular cell to another then the formula will "=AVERAGE (A1:A4)".
SUM: The SUM function is used to add the cell numbers within a range. For example: To calculate the sum from the cell A1 to A8 you will use the formula "=SUM (A1:A8)".
MAX: The function MAX returns the maximum value within the range. To use the MAX function the formula will be "=MAX (B2:B8)".
MIN: The MIN function is used to find the minimum number within the range or the selected cells. For example: "=MIN (B6:B9)" will be the formula of calculating the minimum number.
MEDIAN: It returns the middle value of the data you want to select. For example: "=MEDIAN (C5:C10)" will be the formula for calculating the median from the cell C5 to C10.
How to Calculate Range in MS Excel
Step 1: Open MS Excel
To open the MS Excel application on your desktop, navigate to the MS Excel icon and give it a click.
Click on the MS Excel icon to open appStep 2: Select the Cell
To find the range within the particular cells select a cell which will display the output of the Range function.
Select the CellStep 3: Enter the Range Function, Select the Range and Press Enter
Now in the selected cell enter the range function directly including the cell reference of which you want to find the range. For example: If you are having data stored in the cells from A1 to A8 then the formula will be "=RANGE(A1:A8)". After entering the range function press the "Enter" button to calculate the range.
Enter Range Function >> Press EnterCalculating Range in Excel
There are many ways to calculate the range in Excel within the numbers. Here we will be calculating range of the numbers with different formulas.
How to Find Range in Microsoft Excel Using MAX and MIN
Step 1: Open MS Excel
To open MS Excel application on your desktop navigate to the MS Excel icon and give it a click.
Click on the MS Excel icon to open the appStep 2: Select the Cell
Now select the cell in which you want to store the output of the range function as you have to use the Range formula in the particular cell.
Select the CellIn the selected cell enter the formula of the range function which is known as the difference between the maximum and minimum values within the selected cells. So, the final formula for finding the range will be =MAX (Cell References)-MIN (Cell References), for example: If the cell from A1 to A6 are filled with the datasets then the formula will be =MAX (A1:A6)-MIN (A1:A6). Remember to press "Enter" after entering the formula in the cell.
Enter Range formula of MAX and MIN
How to Calculate Range using the SMALL and LARGE Functions
Step 1: Open MS Excel and Select a Cell
To calculate the range of your particular dataset using the SMALL and LARGE function, open MS Excel on your desktop and select a cell to store the output of the formula.
.webp)
Select a CellStep 2: Find SMALL and LARGE VALUES
First you need to find the small and large values of your dataset, to do so enter the formula "=SMALL (A1:A10, 1)" to find the smallest value and "=LARGE (A1:A10, 1)" to find the larger value in the dataset.
For the Large value:
Use Large functionFor Small Value: Use "=SMALL ("A1"A10,1) to calculate the smallest number in your dataset.
Use Small FunctionStep 3: Subtract the Values
Now to find the range of the number subtract the larger value from the smallest value and then you will be able to find the range of the dataset.
Preview Result
Step 1: Open MS Excel
To open MS Excel application on your desktop navigate to the MS Excel icon and give it a click.
Open MS ExcelStep 2: Select the Cell
Now select the cell in which you want to store the output of the range function as you have to use the Range formula in the particular cell.
Select a CellTo calculate the range of your particular dataset using the conditional MIN and MAX formulas use the "=MAX (IF (A1:A10>5, A1:A10)) - =MIN (IF (A1:A10>5, A1:A10))" formula as range is referred as the difference of maximum and minimum value.
Use the Formula
Conclusion
Finding range in MS Excel helps you in having a corresponding value within your data and there are two simple ways by which you can find the range of the particular cells. In the above article we have explore both the methods of finding range and you can go through with any of the method mentioned above.
Similar Reads
How to use MINIFS function in Microsoft Excel
The MINIFS function in Excel is a powerful tool for filtering and analyzing data. It allows you to find the smallest numeric value in a range of cells that meet one or more specified requirements. This can be useful for a variety of tasks, such as finding the lowest price for a product in a specific
11 min read
How to Find Outliers in Excel?
Outliers as the name suggest are something that doesn't fall in the required/given range. Outliers in statistics need to be removed because they affect the decision that is to be made after performing the required calculations. Outliers generally make the decision skewed i.e they move the decision i
4 min read
Workbooks in Microsoft Excel
Spreadsheet programs have become essential for many organizations for analyzing and storing data and if we talk about the most recognized spreadsheet program, then it will be MS Excel. Now, if you have worked with Microsoft Excel, then you have heard about the terms workbooks and worksheets. In the
5 min read
How to Use the SUBTRACT Function in Excel
Subtraction in Microsoft Excel - Quick StepsSelect the cell where you want the result.Type = (equal sign)> then select the first cell to subtract from.Type - (minus sign) > Select the cell you want to subtract.Press Enter.Subtraction in Excel is a basic yet powerful skill that can make your wo
10 min read
How to Find the Intersection of Two Curves in Excel?
The intersection of the two curves is the point where the two curves meet, and their coordinates are the same. In this article, we will learn about how to find the intersection point of the two curves in excel. Finding the intersection point of two curves can be very useful in data analysis. Let's l
3 min read
How to add Filters in MS Excel?
Microsoft Excel is software that allows users to store or analyze data in a proper systematic manner. It uses spreadsheets to organize numbers and data with formulas and functions. If your worksheet contains a lot of content, seeking out information quickly can be difficult. Filters are often used t
4 min read
How to Use Find and Replace Text and Numbers in Excel
Have you ever spent hours scrolling through rows in Microsoft Excel, searching for that one typo or outdated number buried in your spreadsheet? You’re not alone. Manually hunting for data is time-consuming and error-prone—but what if you could fix mistakes or update information in seconds? That’s wh
7 min read
How to Use For Next Loop in Excel VBA?
If you are familiar with the programming you must have an idea of what a loop is, In computer programming, a loop is a sequence of statements that are repeated until a specific condition is satisfied. In Excel VBA the "For Next" loop is used to go through a block of code a specific number of times.
4 min read
How to Calculate the Midrange in Excel?
MS Excel is a spreadsheet developed by the company Microsoft. Excel provides various kinds of functions and we can insert the data in form of rows and columns and perform operations on the data and yield the results we desired. The Midrange of the dataset in other terms can be specified as average o
2 min read
How to Show Formulas in Excel
Exploring the art of Excel formulas can turn up your data analysis and reporting capabilities. Whether you’re a financial analyst, digital marketer, or an Excel learner, understanding how to display formulas in Excel is important. This article will make you learn the simple steps to show the formula
4 min read