When an Excel user refers to a range, they are referring to an individual cell or a group of cells within an excel worksheet.
The term ‘Range’ is also used in mathematics. (In mathematics, range is the value between the minimum and maximum of a data set.)
We will explain how to locate range in Excel, how to use range in Excel formulas, and the difference between Excel table and range. But first, define the range in Excel.
As previously stated, when a user refers to a range in Excel, they may refer to a single cell or a group of cells.
Ranges within a dataset can also be non-adjacent or adjacent cells.
So, what is the range? A range is a collection of value spreads between the lowest and maximum. Or we might argue that range defines the value spread inside a dataset.
The range’s basic formula is:
Range = Maximum – Minimum
Assume you pick 10 numbers at random from the excel list. To find the range of numbers, you need to use the MAX and MIN functions.
Subtract the MIN from the MAX to get the number’s minimum and maximum. The range is the result.
What are the two types of Excel ranges?
Using Excel to find a range
Let’s look at an example of how to use the range in Excel. Assume you have a list of values like:
15, 39, 12, 48, 20, 5
As you can see, the dataset has a maximum of 60 and a minimum of 5.
So, 60 – 5 = 55.
This is how a simple formula calculates the dataset’s range. The same procedure in excel. The main difference is that the excel worksheet’s MIN and MAX functions can check the range.
Range in Excel Formula
Many organizations do not have time to sort data into columns and rows. A list of sales, revenue, or other valuable information.
The data group’s lowest and highest figures help in decision-making, forecasting, and budgeting.
Excel provides different ways to write range formulas that function according to the user’s demands and times. So, let’s see how to discover range in excel formula.
Assume a manager keeps track of product sales data such as model, unit price, state, unit count, and total revenue.
The past sale data will be as follows:
It’s now possible to determine product demand. You can easily check the MAX() and MIN() values using the formula below.
- Enter “=MAX(C2:C13)” in B16.
- Enter “=MIN(C2:C13)” in B15.
As you can see, Iowa sold the fewest devices at 102 tablets, while Illinois sold the most at 450 laptops.
BK and TK Formula
Assume you have three lowest-selling items in your data. Before using the SMALL () functions, you need two things:
- A list or range of numbers for MIN ().
- The required k position from the bottom. [To find the least number, use k = 1. [K = 2 for the second smallest value, etc.]
- LARGE() with k = 1 gives the same result as MAX ().
- SMALL() with k = 1 gives the same result as MIN ().
- Maximum and Minimum Conditions
There are occasions when you need to examine a minimum value that meets the criteria.
Assume you need to examine the lowest units sold in the spring quarter.
Use conditional formulas like COUNTIF() and SUMIF() here. [But there is no MAXIF() and MINIF(), so you need to design a different array]. This array formula helps users analyze ranges of cells rather than individual cells.
Normally, the IF() formula is used to examine each cell’s value. But it can examine every cell in the range.
When using the array formula, enter #VALUE! Remember to use CTRL+SHIFT+ENTER after finishing the array formula.
In this case, the desktops’ minimal value is found. For this, enter the match value in the cell.
B18: desktop Now the formula compares the reference cell. Now type MIN(IF(B2:B13=B18,C2:C13)) in IF(). then press CTRL+SHIFT+ENTER.
Rows 18, 19, and 20 will show the results of the three formulas. These are laptops, desktops, and tablets.
Users can also use the same method with MAX() to determine the most sold product.
Find range in Excel!!!
Table vs Range in Excel
Cells are used to check the list’s lowest and maximum values. And the list is a table.
But the table and the range are very different.
Also, users must know the difference between a table and a range.
Don’t worry; we’ve given an excel table versus range comparison table to help you distinguish.
The user can quickly change the table’s format.
The user can alter the range’s format manually.
The table features a slicer feature.
The range does not have the slicer command.
The table header features a filter button.
A user can filter a range using the Filter command in the Data Ribbon.
As the table is scrolled down, the header easily replaces the column headers.
When scrolling down, the column headings cannot replace the headers.
The table can be readily expanded.
The data range cannot be expanded.
The table easily supports structed references
Range does not support Structured References.
Excel tables are considered as Objects.
Excel does not treat ranges as objects.
For example, a user can utilize a formula in a column and copy it to other cells within the same column.
So let’s say a user wishes to employ a formula. Double-click the AutoFill Handle tool, then copy the formula to each column. Keep in mind that the formula is not duplicated automatically.
The range is the difference between the lowest and maximum number limits in excel. We have shown you how to find range in Excel using three ways.
It can check the list’s maximum and minimum values. Also, people may be confused between Excel table and range, so we have supplied a comparison table. See the table to find out.
If you have any questions on how to locate range in excel, please leave a comment below. How to find the range of cells in excel?
We constantly do our best to assist our readers. So, don’t lose time; apply the above Excel formulas to improve your skills!!!
If you need help with an excel assignment, contact our professionals.