The concept of a cell range is one of the most important ideas in Excel. A range is a rectangular group of cells. For example, you can refer to the first 10 cells in column A with a range like this:
=A1:A10 // first 10 cells in column A
In Excel, the colon (:) is a range operator, and separates the first reference from the last reference in a range. If a range includes just one cell, there is no need to use a colon (:) and a second reference. For example, both references below are the same:
=D2:D2
=D2
Ranges can be horizontal or vertical, or both. The first cell in the range is always the upper left cell, and the last cell in the range is the lower right cell:
=A1:F1 // horizontal range
=A1:A5 // vertical range
=A1:C3 // 2d range
Ranges can include full columns or rows. To refer to all of column A, you can write:
=A:A // all of column A
To refer to all of row 1, you can use a range like 1:1:
=1:1 // all of row 1
Relative or absolute
Like cell references, ranges can be relative, absolute, or mixed:
=A1:A10 // relative
=$A$1:$A$10 // absolute
=$A$1:A2 // mixed
The easiest way to toggle between available options is to use a keyboard shortcut
Ranges in formulas
Ranges are commonly used in all kinds of formulas. Ranges also map perfectly to arrays, a programming concept used in more advanced formulas.
Named ranges
Ranges can be named using the name box. Named ranges are an easy way to make a formula easy to read and understand. Named ranges also behave like absolute references by default, so they are a handy way to lock certain ranges in a formula that needs to be copied.
