How to Calculate Range in Excel

The range of a particular data set gives you a simple picture of the spread of values it contains. The range is useful as a starting point in analyzing your data and is 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.

The range is a simple measure that tells you 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 4, 2, 5, 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 suggests data that is closely centered around a specific value.

The key to finding a range in Excel is to obtain the minimum and maximum values of the data set. Find the minimum by typing "=MIN" into a cell. Either double-click where it says "MIN" in the drop-down list or type a "(" to start. Inside the parentheses, type 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 holding 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 find the maximum value from the data set by typing "=MAX" into a cell and then going through the same process as for the "MIN" function.

The easiest method for using 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.

You can also use the formulas above for minimum and maximum 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 tells Excel to find the maximum of the data and then subtract the minimum of the data from it.