Excel Column Total

Updated on December 27, 2023
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Total Column in Excel

Usually, numbers are stored in rows of a single column, so getting the total of those columns is important. However, there are different ways of getting the total. As a beginner, it is important to know the concept of getting the column totals. This article will show you how to get excel totals of columns differently.

How to Get Column Total in Excel (with Examples)

Here we will show you how to get total columns in Excel with a few examples.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

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

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

If you need a quick total of the column and see 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.
Column Total Example 1
  • To get the total of this column “B,” select the entire column or the data range from B2 to B6. Then, we need to select the whole column and see the “Status Bar.”
Column Total Example 1-1

As we can see in the status barStatus BarAs the name implies, the status bar displays the current status in the bottom right corner of Excel; it is a customizable bar that can be customized to meet the needs of the user.read more, we have a quick sum showing as 26,356.

  • Instead of selecting the entire column, we must choose the only data range and see the result.
Column Total Example 1-2

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

Example #2 – Get Auto Column Total in Excel

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

  • We must select the cell which is just below the last data cell.
Auto Column Total 1
  • Now press the “ALT + =” shortcut key to insert the AUTOSUM option.
Auto Column Total 1-1
Auto Column Total 1-2

Since we have selected only the data range, it has given us the same 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.
Auto Column Total 1-3
  • In the above data, we have data range till the 11th row. So in the 12th row, we need the column total, so select cell B12.
Auto Column Total 1-4
  • After that, we need to press the ALT + = sign shortcut key to insert the quick SUM function.
Auto Column Total 1-5

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

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

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

  • We can open the SUM function in any cell other than the column of numbers.
Using SUM Function 1
  • For the SUM function, choose the entire column of “B.”
Using SUM Function 1-1
  • Then, close the bracket and press the “Enter” key to get the result.
Using SUM Function 1-2

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

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

The SUBTOTAL function in excelSUBTOTAL Function In ExcelThe SUBTOTAL excel function performs different arithmetic operations like average, product, sum, standard deviation, variance etc., on a defined range.read more is very powerful to show only displayed cell results.

  • We must open the SUBTOTAL function first.
Using Subtotal Function 1
  • Upon opening the SUBTOTAL function, we have many kinds of calculation types. From the list, choose the 9-SUM function.
Using Subtotal Function 1-1
  • Next, choose the above range of cells.
Using Subtotal Function 1-2
  • Then, close the bracket and press the “Enter key” to get the result.
Using Subtotal Function 1-3

We have got the total of the column. But the question is, if the SUM function returned the same, then what is the point of using SUBTOTAL.

  • The special thing is that SUBTOTAL displays the calculation only for visible cells. So, for example, apply a filter and choose only any of the three months.
Using Subtotal Function 1-4
  • We have chosen only the “Aug, Jun, and May” months. Then, click on “OK” and see the result.
Using Subtotal Function 1-5

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

Things to Remember

  • The 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 between, all those values will be deducted, and the result is the same as the mathematical rule.

Recommended Articles

This article has been a guide to Excel Column Total. We learn how to get a column total in Excel using the SUM and SUBTOTAL function and examples and a downloadable Excel template. You may learn more about Excel from the following articles: –