Excel allows you to use a shorthand notation to add up an entire column. Instead of having to drag across thousands of rows to specify a large cell range, you can use this shorthand method to add up all the values in that column. Thus, knowing how to sum an entire column in Excel can save you loads of time in the long run.
To sum an entire column, use the SUM function as usual, but omit the row numbers from the cell range.
- column_letter is the letter name of the column you want to sum
For example, if we want to add the entire column (column A) in a spreadsheet, we could use:
In Plain English
The SUM function adds up all values in the specified range. In this formula, we use the “whole column” reference to specify that the range includes an entire column. Think about it. Omitting the row numbers from the usual range definition means: I want to sum all cells in column A, regardless of their row number.
- You can include a text header in the column without affecting the results. This is because the SUM function ignores any text in the specified range.
- When using this formula, be careful that the row you are trying to sum is not the row containing this formula. In that case, a circular reference warning will be triggered.
- This formula is especially useful when you have a large set of data to which you keep adding rows frequently.
Sum several entire columns at once
You can also sum several entire columns with the SUM function by specifying the first column name as the first part of the range, and the last column name as the last.
This formula will add together all values in column A, B, and C.
Sum an entire row
You can also sum an entire row by specifying the row number without the column name:
This formula will add together all values in row 1.
Sum an entire column except specific cells
If there are specific cells in the column you wish to exclude from the sum total, you can always subtract them afterwards. For instance, if you wish to sum all of column A except cells A1 and A2, you could use the following formula:
=SUM(A:A) – A1 – A2