Exceljet

Quick, clean, and to the point

Full row reference

Example of full row references in a SUM formula

Excel supports both full column, and full row references. A full row reference is a reference that refers to an entire row. For example, to sum all of the values in row 1, you can use a formula like this:

=SUM(1:1)

Notice a full row reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no columns in a full row reference, the literal translation of the range 1:1 is "every cell in row 1". Like other ranges, a full row reference can include multiple rows. For example, to reference rows 1 through 5:

=SUM(1:5)

In the example shown above, the formula in cell C8 sums all quantities in row 5:

=SUM(5:5)

Here, 5:5 is a range that includes all 16,384 columns in a worksheet, so it includes 16,384 cells.

Pros and cons

Full row are easy to enter, and one big advantage of full row references is that they always include every cell in a row, so there is no need to update the reference if data is added later to a worksheet. Also, full row references are a very compact and easy-to-read way to represent a generic range, so they can be useful when explaining certain formulas.

However, when using full row references, you need to take care to ensure there aren't stray values in other columns to the right (or left) of the target values. Any value, no matter how far down the worksheet, will be included. In addition, full row references can cause performance problems in some worksheets, since Excel can bog down calculating many thousands of unnecessary cells.