Range is one of the most useful and common data measurements. It measures the difference between the minimum and maximum values in a given dataset. Let’s learn how to calculate range in Excel.
How to Calculate Range in Excel (With Formulas)
Excel offers two easy ways to find the range of your dataset. Consider a table of data showing past sales. You want to find the range of the sales figures.
Click into an empty cell, then type = to begin a formula. Then, type MAX(. Click and drag to select your dataset, in this case, the Sales Price column.
Close with ).
Your formula will look like this:
=MAX(A2:A10)
Then press Enter on your keyboard. This gives you your maximum value.
Now, you need to find the minimum value. The process is very similar. In another empty cell, input the following formula:
=MIN(A2:A10)
When complete, you’ll have both values. To find your range, simply subtract the minimum value from the maximum value.
In another empty cell, type =, then click on your maximum cell. Type -, then click on the minimum cell.
=D1-D2
Pressing Enter returns the value of $707,000, which is your range.
How to Calculate Range (With Filters)
If you have a smaller data set, or don’t want to use formulas, you can find the range of a dataset with the help of filters.
Click into cell A1, Sales Price. On the Home tab, click on Sort & Filter. Click on the Filter icon.
Next, click the dropdown arrow that appeared in your cell. On the dropdown, choose the second option: Descending (or Sort Largest to Smallest). The top number is your maximum value; the bottom number is your minimum.
To compute range, find an empty cell and subtract the bottom value from the top value. Here, your formula would look like this:
=A2-A10
Press Enter, and you’ve computed the range of your data.
Excel makes finding the range of data a breeze, thanks to formulas and filters.
Copy a Formula in Excel