The Formula Bar in Excel sits directly above the worksheet area, to the right of the Name Box . The formula bar can be used to edit the content of any cell and can be expanded to show multiple lines for the same formula ( example , shortcut for toggling ).
For an example of editing a long formula in an expanded formula bar, watch this video .
Excel supports both full column, and full row references. A full column reference is a reference that refers to an entire column . For example, to sum all of the values in column A, you can use the SUM function like this:
=SUM(A:A)
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:
=SUM(A:D)
In the example shown above, the formula in cell G5, copied down, used two full column references:
=SUMIF(B:B,F5,C:C)
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 .
