In Excel, range means one of two things, and which one depends on the question. A cell range is a rectangular block of cells, written from its top-left cell to its bottom-right cell as A1:B10, and it is what a formula reads when you write =SUM(A1:A10). The statistical range is a single number, the largest value in a set minus the smallest, which you calculate with =MAX(range)-MIN(range). This page takes the two meanings in turn, then looks at what happens when a range tucked inside a formula ends up covering the wrong cells.
What is a cell range in Excel?
A cell range is a group of cells you point at as one thing. You write it as the top-left cell, a colon, and the bottom-right cell, so A1:B10 is the whole block that runs from A1 in the top-left corner down to B10 in the bottom-right, taking in every cell between the two. Microsoft’s definition is broad: “A reference identifies a cell or a range of cells on a worksheet, and tells Excel where to look for the values or data you want to use in a formula.” A range is simply the version of that address that covers more than one cell.
The : between the two corners is the range operator, the character that turns two single addresses into one block. The rest of the signs that show up in a formula are collected in the formula symbols glossary.
The shape of a range depends on which corners you give it:
A1:A10is a single column, ten cells running straight down.A1:E1is a single row, five cells running across.A1:B10is a rectangular block, two columns wide and ten rows deep, twenty cells in all.
All three are ranges, and the notation is the same in each case: top-left corner to bottom-right corner.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Test 1 | Test 2 |
| 2 | Ada | 82 | 88 |
| 3 | Bo | 91 | 79 |
| 4 | Cy | 74 | 95 |
When you hand a range to a function, that range is what the function works on. =SUM(B2:B4) reads the single-column range B2:B4 and adds the three Test 1 scores to 247. That is all a formula is doing when it names a range: reading a block of cells, then calculating over it. What a formula is, and how you enter one, has its own guide.
How do you select a range in Excel?
You select a range two ways. Type its reference, such as A1:B10, into the Name Box at the left of the formula bar and press Enter, and Excel selects that exact block. In Excel for the web, clicking a cell and dragging across the neighboring cells extends the selection to cover the range.
A range reference can also be pinned with dollar signs, $A$1:$A$10 instead of A1:A10, so a fill-down keeps reading the same block. That locking is the whole subject of cell references in Excel.
What is the statistical range, and how do you find it?
The other meaning of range has nothing to do with cell addresses. In statistics, the range of a set of numbers is its spread: the largest value minus the smallest, reported as a single number. A group of test scores from 74 to 95 has a range of 21, however many scores sit in between.
Excel has no dedicated RANGE function, so you build the calculation from two functions it does have. MAX returns the largest number in a range and MIN returns the smallest, so subtracting one from the other gives the spread:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Test 1 | Test 2 | Range | |
| 2 | Ada | 82 | 88 | 21 | |
| 3 | Bo | 91 | 79 | ||
| 4 | Cy | 74 | 95 |
MAX and MIN look only at the numbers in the range. They ignore empty cells, text, and logical values, so a stray label or a gap in the middle of the block does not throw the answer off, and only the real numbers count toward the largest and the smallest.
The direction matters: the range is always MAX minus MIN, never the reverse. MIN minus MAX would return a negative number, and the spread of a set of values is never negative. The range is also a single value, not the cells themselves, which is where it parts company with a cell range.
If you would rather not type the functions, the same MAX and MIN sit on the AutoSum button. On the Home tab, select the arrow next to Sum in the Editing group and choose Max or Min, and Excel writes the formula for you. The button gives you one or the other, so you still subtract the smaller result from the larger to reach the range. AutoSum covers the button in full.
The words sit close together, so one more distinction helps: the statistical range tells you how far apart the numbers are, while counting how many cells in a range hold a value is a different question, answered by COUNT, COUNTA, and COUNTBLANK. The spread and the tally are not the same measurement.
Cell range, named range, and statistical range
Three things share the word range, and keeping them apart clears up most of the confusion:
- A cell range is the block of cells, addressed by its corners, like
B2:C4. - A named range gives that same block a plain name, so
Scorescan stand in forB2:C4in a formula. Creating and managing names is its own guide. - A statistical range is the spread of the numbers,
=MAX(range)-MIN(range), a single value.
The first two are about where the data is; the third is about what the data does. A formula like =MAX(Scores)-MIN(Scores) leans on all three at once: a named range, Scores, that points at a cell range, B2:C4, fed into the statistical range.
When a formula’s range covers the wrong cells
Because a range is just an argument, the block a formula reads is one keystroke away from changing. Add a row of data below a total and the SUM above it may still stop at the old last row; trim a reference by hand and a value drops out. =SUM(A1:A10) becomes =SUM(A1:A9), and the total recalculates to a smaller figure with no error value to say a row went missing:
The short version
- A cell range is a rectangular block of cells, written top-left corner to bottom-right corner as
A1:B10.A1:A10is one column,A1:E1is one row, and the colon between the corners is the range operator. - Select a range by typing its reference into the Name Box, or by dragging across the cells in Excel for the web. A range is what a function reads, as in
=SUM(A1:A10). - The statistical range is the spread of a set of numbers: the largest minus the smallest,
=MAX(range)-MIN(range), a single value. For scores from 74 to 95 it is 21. MAX and MIN ignore blanks and text. - A named range gives a cell range a name,
ScoresforB2:C4, which is the subject of its own guide.
A range is quick to write and quick to mis-set: one keystroke moves where a total’s block begins or ends, and only the number changes. To see which cells a formula’s range picked up or dropped, and which totals moved with them, compare the edited model against the version before the change.
