Exceljet

Quick, clean, and to the point

Range

Examples of ranges in Excel

The concept of a cell range is one of most important ideas in Excel. A range is a rectangular group of cells. For example, you can refer to the first 10 cell 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, 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 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.