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.
A relative reference in Excel is a pointer to a cell or range of cells. For example, a relative reference to cell A1 looks like this:
=A1
A relative addresses will change when copied to other location in a worksheet because it describes the “offset” to another cell, rather than a fixed address. To help understand what this means, consider the phrase “the house next door to the right”. You can only understand the location of this house if you understand the starting point, because the location is described in relative terms.
By default, all references in Excel formulas are relative. You can convert a relative reference to absolute reference with by using dollar sign ($) characters.
Example
In the example shown, the formula in E4 contains two relative references that will change as follows when copied down column E:
=C4*D4
=C5*D5
=C6*D6
=C7*D7
=C8*D8
