Excel Column Total

Total Column in Excel

Usually, numbers are stored in rows of a single column, so getting the total of those columns is important. There are different ways of getting the total, as a beginner, it is important to know the concept of getting the column totals. In this article, we will show you how to get excel totals of columns in different ways.

How to Get Column Total in Excel (with Examples)

Here we’re going to show you how to get total columns in excel with a few examples.

You can download this Column Total Excel Template here – Column Total Excel Template

Example #1 – Get Temporary Excel Column Total in Single Click

Do you need a quick total of the column and see what is the total of any column then this option will show a quick sum of numbers in the column.

For example, look at the below data in excel.

In order to get the total of this column “B” just select the entire column or the data range from B2 to B6, first select the entire column and see the “Status Bar”.

As you can see in the status bar we have a quick sum showing as 26356.

Now instead of selecting the entire column select the only data range and see the result.

Total is the same but the only difference is the formatting of numbers. In the previous case, we have selected the entire column and the formatting of numbers was not there but this time since we have selected only numbers range in the quick sum too we can see the same formatting of the selected range.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Example #2 – Get Auto Column Total in Excel

The temporary total can be just seen but to make the totals permanently visible in a cell we can use the AUTOSUM option. For example in the above data, we have data till the 6^th row, so in the 7^th row, we need the total of above column numbers.

Select the cell which is just below the last data cell.

Now press the shortcut key “ALT + =” to insert the AUTOSUM option.

As you can see above it has inserted SUM function in excel automatically upon pressing the shortcut key “ALT + =”, hit enter key to get the column total.

Since we have selected only data range it has given us the same formatting of the formatting of the selected cells.

However, there are certain limitations to this AUTOSUM function especially when we work with a large number of cells. Now, look at the below data.

In the above data, we have data range till the 11^th row so in the 12^th row we need the column total, so select the cell B12.

Now press the shortcut key ALT + = sign to insert quick SUM function.

Look at the limitation of AUTOSUM function it has selected only 4 cells above, this is because after the 4^th cell we have a blank cell so AUTOSUM shortcut thinks this will be the end of the data and returns only 4 cells summation.

In this case, this is a small sample size but when the data is larger it may go unnoticed. This is where we need to use the manually SUM function.

Example #3 – Get Excel Column Total by Using SUM Function Manually

Open SUM function in any of the cells other than the column of numbers.

For SUM function choose the entire column of “B”.

Close the bracket and hit enter key to get the result.

So this will take the entire column into consideration and display results accordingly. Since the entire column has been selected we need not worry about any missing cells.

Example #4 – Get Excel Column Total by Using SUBTOTAL Function

The SUBTOTAL function in excel is very powerful to show only displayed cell results.

Open SUBTOTAL function first.

Upon opening the SUBTOTAL function we have many kinds of calculation types, from the list choose 9-SUM function.

Next, choose the above range of cells.

Close the bracket and hit enter key to get the result.

We have got the total of the column but the question is the same was returned by SUM function as well then what is the point of using SUBTOTAL.

The special thing is SUBTOTAL displays the calculation only for visible cells, for example, apply filter and choose only any of the three months.

I have chosen only “Aug, Jun, and May” months. Click on “Ok” and see the result.

The result of a SUBTOTAL function is limited to only visible cells not the entire range of cells, so this is the specialty of SUBTOTAL function.

Things to Remember

AUTOSUM takes the only reference of non-empty cells and at the finding of empty cells, it will stop its reference.
If any minus value is there in between all those values will be deducted and end result is exactly the same as the mathematical rule.