How to Sum a Column in MS Excel

You may need to get the total of all your numerical data. There are many methods of getting the sum of the numerical data in a column. Each of the methods below work best in different scenarios. Therefore, study all of them to know the method that will work best for you.

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. The status bar doesn’t allow you to copy the sum. Also, the sum in this bar cannot be used for further calculation. However, it can be very useful when you only need to see the sum of the column. Follow the step given below to get the sum of a column using the status bar.

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

When you sum data, you will get complete information about the data. The methods above will be useful in various situations. 

For example, the first method will let you know the sum of a column in a few seconds. This can be useful when you need information about a column while conversing with your team.

The other methods allow you to manipulate data further, once you have the sum total on your spreadsheet.

Leave a Comment