When an excel user talks about the range in excel, it is obvious that he/she must be referenced to the individual cell or to the collection of the cell within the excel worksheet.
People might get confused with the term ‘Range’ as it is used in mathematics also. (In mathematics- Range is the value that lies between the given data’s minimum and maximum value).
In the blog, we will help you to understand how to find range in excel, range in excel formula, and what the difference is between excel table vs range. But before proceeding to more details about range in excel, let us know what the range is.
What is the Range?
As we have already mentioned that when a user talks about the range in excel, then there is a possibility that he/she talks about the single cell or the group of cells.
Moreover, the range can be non-adjacent or adjacent cells within the dataset.
Now, the question is, what is the range? Well, a range is the combination of various value spreads that lies between the minimum and maximum value. Or we can say that range is used to define the spread of values within the datasets.
The basic formula of the range is as:
Range = Maximum Value – Minimum Value
For example, suppose you select 10 different numbers randomly within the list of excel. Now, to find out the range of the numbers, you require to find out the maximum or upper and minimum or lower value using MAX & MIN functions.
When you get the minimum and maximum value of the number, then subtract the MIN value from the MAX value. The result is known as the range.
What are the two types of ranges used in Excel?
|Symmetrical Range||Irregular Range|
|It is the range of the cells that are located at the adjacent position to one another. This kind of range usually looks like a rectangle or square within the spreadsheet whenever they are highlighted. The below image shows the range as (A1:C4).||A range consists of cells that are not contiguous or might have irregular geometrical shapes whenever they are highlighted. In the below image, the highlighted cells are: (A1:C4, E1, E4, B6).|
How to find the range in excel?
To know the exact procedure of the range in excel, let’s take an example. Suppose you have a list of different values as:
19, 12, 48, 20, 5, 60, 15, 39
Now, you can see that the largest number in the given dataset is 60, whereas the smallest number of the dataset is 5.
Therefore, the range will be 60 – 5 = 55.
This is how you calculate the range of the dataset in mathematics with a simple formula. The same procedure you need to follow in excel. The only difference is that you can check the range using the MIN and MAX function in the excel worksheet.
How to use range in excel formula?
Several businesses do not have enough time to waste sorting the data through columns and rows. The rows and columns have the lowest and highest values of sales, revenues, or other useful information.
The lowest and highest figures within the data group are useful for accurate decision-making, forecasting, and budgeting.
It is known to all that excel provides various ways for writing the range formulas that can work as per the user needs and required times. So, let’s check the range in excel formula or how to find range in excel.
MIN and MAX Formula
Suppose a manager keeps the product sale data that include model, unit price, state, number of units, and the total revenue of every product/state.
The product sale data of the previous sale will be as follows:
Now, you can find which product has the greatest and smallest demand. Using the below formula mentioned, you can easily check the MAX() and MIN() values.
- In cell B16, type “=MAX(C2:C13)”.
- In cell B15, type “=MIN(C2:C13)”.
Now, you will notice that the smallest number of units sold in the respective state is 102 tablets in Iowa, whereas the largest sold is 450 laptops in Illinois.
Bottom k and Top k formula
Suppose you have multiple data that has details as three different lowest-selling items. You can use the SMALL () functions, but before that, you require two factors:
- A similar list or range of the values that you will use for MIN().
- The specific value of k, which is the required position from the bottom. [If you want to check the smallest value, then the value of k will be k = 1. For the second smallest value, k = 2, and so on.]
- The LARGE() with k = 1 provides a similar result as that of MAX().
- Whereas the SMALL() with k = 1 will give the same output as MIN().
3. Conditional Maximum and Minimum Formula
Sometimes, you need to check the minimum value, which can easily meet the particular criteria.
Let’s take an example; suppose you require to check the lowest units sold in the spring quarter.
Here, you can use COUNTIF(), SUMIF(), and other conditional formulas. [But there is no MAXIF() & MINIF(); therefore, you need to create a different array to find Max and Mini statement functions]. This array formula will help the user evaluate the specific cells’ range rather than the individual cell.
Usually, the IF() formula is used to test the real value of the individual cell. But users can use it to evaluate every cell within the range.
When you use the array formula and press enter, it will show #VALUE!. Therefore, always keep in mind that you have to select CTRL+SHIFT+ENTER once you finish the array formula.
In this example, you can find the minimum value of the desktops. For this, write the match value in the cell to compare it with the functions.
Type “desktop” in B18. Now, the formula compares the testing cell reference. Now, type IF() statement as: “=MIN(IF(B2:B13=B18,C2:C13))” and select the <CTRL>+<SHIFT>+<ENTER>.
The calculated value of the three formulas will display in rows 18, 19, and 20. This shows that the minimum sell number is of laptops, desktops, and tablets.
Moreover, users can use the identical formula with MAX() to get the largest product sold.
This is how to find range in excel !!!
A Comparison Table: Excel Table vs Range
We know that the range of cells is used to check the list’s minimum and maximum values. And the list is created in the form of a table.
But there is a major difference between the table and the range.
Moreover, there is a necessity that users must know the difference between a table and the range.
Do not worry; we have provided a comparison table of excel table vs range that helps you to know the difference.
|The format of the table can be changed easily by the user.||The format of the range can change manually by the user.|
|The table feature has the slicer command.||The slicer command is not available in the range.|
|The header of the table has the filter button.||If a user wants to filter a range, then he/she can do it using the Filter command available in the Data Ribbon.|
|When a user scrolls the table down, the header easily replaces the common column headings.||In the case of range, the headers can not be replaced by the column headings whenever the user scrolls down.|
|Users can expand the table very easily.||Users can not expand the range of the data.|
|Structured References easily supported by the table.||Structured References are not supported by the range.|
|The tables in excel are treated as the Object.||In excel, the range does not treat as an Object.|
|If a user wants to use a formula in a column, then the formula can be copied to other cells as well within the same cell.||Suppose a user wants to use a formula in a column. He/she needs to double click on the AutoFill Handle tool, then copy the same formula to each column. You have to keep in mind that the formula does not get copied by itself.|
Basically, the range is the difference between the minimum limit and the maximum limit of the available numbers in excel. We have provided the details about how to find range in excel using three different methods.
Users can use it to check the maximum and minimum values from the list. Moreover, users might be confused with the excel table and range, so we have provided a comparison table of excel table vs range. Check the table to know the difference.
If you still have any issue or query regarding how to find range in excel, comment in the below section. We will help you to get the details about the range of cells in excel.
We always help our readers with their queries in the best possible way. So, do not waste your time here and there; use the above-mentioned excel formulas to enhance your skills!!!