When you calculate range in excel, you’re calculating the range of any particular data set. 

This range helps in giving you a simple picture of the spread of values it contains. The range is useful as a starting point not only in analyzing your data but is also something you might need to consider if you’re producing something like a box and whisker plot. Finding the range in Excel is a relatively simple process that can be achieved using the “MIN” and “MAX” functions built into the program.

Learn To Calculate Range in Excel

What Does Range Mean?

A range is just a measure of values that lets you know the spread of values in a data set. It has a simple definition:

Range = maximum value – minimum value

So if you have a set of data such as 3, 2, 7, 8, 12, 15, the range is the highest number (15) minus the lowest number (2). In this case:

Range = 15-2 = 13

This example tells you that the data set spans 13 numbers. In a box and whisker plot, the ends of the whiskers give you a visual indication of the range, because they mark the minimum and maximum values. A large range suggests a wide spread of results, and a small range usually means data that is closely centered around a specific value.

Let’s say you want to find out who has the smallest error rate in a production run at a factory or the largest salary in your department. There are various methods to calculate the smallest or largest number in a range.

If the cells are in a contiguous row or column then: 

  1. Pick or select a cell below or to the right of the numbers for which you want to find the smallest number.
  2. On the Home tab, in the Editing group, find and click the arrow next to AutoSum, click Min (calculates the smallest) or Max (calculates the largest), and then hit ENTER.

The MIN and MAX Functions

The answer to “how to calculate range in excel” has to do with Min and Max functions. The goal is to obtain the minimum and maximum values of the data set. 

  • Find the minimum by typing “=MIN” into a cell. You can choose to double-click where it says “MIN” in the drop-down list or instead type a “(” to start. 
  • Within or Inside the parentheses, type in the column letter and row number of the first data point. If your data is in a single row or column, type a colon followed by the letter and number corresponding to the last data point and then close the parentheses to return the minimum value. 
  • You can also do this by clicking the appropriate cell after opening the parentheses and then pressing down “Shift” and clicking the cell with the last data point before closing the parentheses. 
  • If the data is spread out irregularly, you can specify each cell individually, separating each one with a comma. For example, you might type “=MIN(A2, F3, H12, J1)” to find the minimum of the values in cells A2, F3, H12 and J1.
  • Use the MAX” function to calculate the maximum value from the data set by typing “=MAX” into a cell then going through the same process as for the “MIN” function.

Range in Excel in Steps

The simplest way to use Excel for range is to perform the calculation in steps. Place the minimum value in a cell (for example, in cell E1) and place the maximum value in another cell (F1, for example), following the instructions in the previous step. In another cell, type “=F1-E1” to find the range. This tells Excel to subtract the minimum in cell E1 from the maximum in cell F1, which gives you the range.

Calculating Range in One Step

You can also use the formulas above for minimum and maximum values to calculate range using Excel in a single step. Imagine you have data running from cell A2 to cell A20. Type “=MAX(A2:A20)-MIN(A2:A20)” to find the range in a single step. This in turn lets Excel know how to find the maximum of the data and then subtract the minimum of the data from it.