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: In the example shown above, the formula in cell C8 sums all quantities in row 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. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
