5 methods of summing columns in Microsoft Excel:
- Using the status bar
- Using Excel AutoSum
- Using the SUM formula
- Using the Filter function to sum subtotals
- Converting the dataset into a table
Let us begin.
Method #1: Using the Status Bar
The status bar is at the bottom of the Microsoft Excel page. It displays the Sum by default.
This method is very useful if you just want to view the sum
There are a few limitations with this method though. First is that the status bar doesn’t allow you to copy the sum.
Also, the sum in this bar cannot be used for further calculation.
Step #1: Select the column
Select the column by clicking the alphabet at the top of the column. Immediately, you will see the sum of that column in the status bar.
Method #2: Using Excel AutoSum
Excel AutoSum works like the Sum formula. However, it is much faster since it requires no additional steps. To use Excel AutoSum, follow the steps given below.
Step #1: Select the column
Click the alphabet header at the top of the column you want the sum of.
Step #2: Select the sum location
Place your cursor in the first empty cell below the data in the column you want to sum.
Step #3: Click AutoSum
Click on AutoSum in the top ribbon of the Home tab.
Excel will insert the sum function and the range into the formula and highlight it.
Press the Enter key on your keyboard.
You’ll be able to see the sum of the values in the selected cell.
Method #3: Using the SUM formula
To use the SUM formula manually, follow the steps given below.
Step #1: Select the sum location
Select the cell where you want the total to appear.
You can put it below the column that contains the data or in a different location.
Step #2: Type the formula
You can enter the formula directly into the cell or type the formula into the formula bar.
The formula is written as SUM, followed by the range.
For our example, the formula is
=SUM(B2:B13).Once you have typed in the formula, press Enter on your keyboard.
The total will now appear.
Method #4: Using the Filter Function to Sum Subtotals
This method works for subtotals. But first, you need to filter your table. To do that, follow the steps given below.
Step #1: Filter the table
Click on any cell within the dataset.
Select the Data tab in the top menu bar to change the ribbon.
Select the Filter icon.
Step #2: Click the drop-down arrow
You will now see little drop-down arrows in the header row of all the columns.
Click on the arrow in the header row of the criteria column.
Step #3: Check the right boxes
Ensure the Select All box is not checked.
Check the box that contains the criteria you need to get the sum of.
Click the OK button.
Step #4: Sum the criteria
Select the cell immediately after the row where the sum must show.
Click on AutoSum in the top ribbon of the Home tab.
Excel will insert the sum function and the range into the formula and highlight it.
Press the Enter key on your keyboard.
In our example, Gold was selected as the criteria.
As a result, Microsoft Excel will show the Gold subtotal.
Method #5: Converting the Dataset into a Table
If you need to sum many columns in the dataset, consider converting the dataset to a table. It will also improve the appearance of your dataset at the same time.
Step #1: Select the dataset
Select the last cell in the last column of the dataset, bottom right.
Holding the button down, drag the cursor over the rest of the dataset.
Step #2: Convert the dataset to a table
Press Ctrl + T on your keyboard to format the dataset as a table.
Step #3: Select ‘Total Row’
You will now see the Design tab in the main menu open, with its corresponding ribbon.
Check the Total Row box in the Design tab.
This action will add a new row to the bottom of the table.
As you can see, the total of the values in Column B is now displayed in row number 14.
Conclusion
We hope you found this article useful.