Notice a full column reference is entered like other ranges, with a colon (:) separating the starting point from the ending point. Since there are no row numbers in a full column reference, the literal translation the range A:A is “every cell in column A”. Like other ranges, a full column reference can include multiple columns. For example, to reference columns A through D: In the example shown above, the formula in cell G5, copied down, used two full column references: Here, B:B is the range, and C:C is the sum_range.
Pros and cons
Full column references are easy to enter, and one big advantage of full column references is that they always include every cell in a column, so there is no need to update the reference if data is added later to a worksheet. Also, full column 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 column references, you need to take care to ensure there aren’t stray values in other rows further down. Any value, no matter how far down the worksheet, will be included. In addition, full column references can cause performance problems in some worksheets, since Excel can bog down calculating thousands or even millions of extra cells if the reference includes more than one column. This is a complicated topic, because it depends also on what Excel considers to be the “used range”. In addition, Excel is always evolving with an emphasis on performance improvements, so different versions of Excel can behave differently. Charles Williams, an Excel performance expert, has published some test results here. Personally, I avoid full column references out of old habit, but you will run into them in the wild in many business environments. If your goal is to create a dynamic range, an Excel Table is a good choice. If you need more flexibility, you can create a dynamic named range. 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.
