How do you reference an entire column in excel?
Let’s say that we are dealing with column A in a spreadsheet. Now, in order to refer to all of the cells in column A inside an excel formula, all we would have to do is write the range as “A:A” – this will reference the entire column A . An example of how to actually use this in a spreadsheet will help clarify what we mean.
An example of how to sum an entire column in Excel
Suppose we have the following simple sheet in Excel that just has four values in column A:
How to apply a formula to an entire column in excel?
Now, suppose we want to sum all of the values in that column and store the result in the first cell in column B. And, we want the value for the sum to automatically update even after new values are added to column A.
All we have to do to accomplish this is add this formula to cell B1: “SUM(A:A)”. The SUM function will sum all of the values inside all of the cells in column A. You can see what happens when we input this formula into cell B1 below:
Note that the entire column A is highlighted once that formula is input – this is of course because the range “A:A” is a reference to all of the cells in column A . Now, once we hit enter, we can see the sum of all of those cells in column A in cell B1, which is 40:
What if we want to sum all of the values in the column but display the sum as a cell in the same column?
You may want to display the sum of all of the cells in column A at the very end of column A itself. So, you might try to input the same formula we used above (“SUM(A:A)”) in one of the bottom most cells in column A – let’s just say we choose cell A14:
What is a circular reference in Excel?
As you can see, cell A14 just displays a 0 and an error icon. Why doesn’t it display a “40”, which is the sum of the values in column A? Well, Excel actually considers this to be what is called a circular reference. Because we are using a reference to the entire column of A, and cell A14 is actually a part of that entire column, this is considered a circular reference – since cell A14 is basically referencing itself which makes no sense. For this reason, Excel just puts a 0 in the cell since that is the only thing that makes sense in this scenario.
If you want to display the sum of all the cells in column A in a cell at the end of column A itself, you will be better off by just defining a formula that you think will cover all the cells and any new cells – so something like “=SUM(A1:A10)”.