Blog

How to Sum Entire Column in Excel

How to Sum Entire Column in Excel
Blog

How to Sum Entire Column in Excel

Typically having tons of data need you to organize it and get the sum of the entire row or column. For instance, if you are having a list of sales data including sales from the first day till now, you must have to get the sum of the sales value starting from day one till now.

Definitely, it would be a tiring thing if you do it manually. That’s why Excel makes this task convenient to you so that you can have the values ready for further proceedings without making errors in the computation.

Simply putting a relevant formula will give you the entire result in just one click. Here in this post, we will try to cover different ways that let you find Excel sum entire column or row. All these methods are tried and proved that’s why you can trust each method without any hesitation.

Here we have 5 different ways that let you know how you can excel sum entire column quickly. These methods use Excel SUM and Excel AutoSum functions. Below are the five ways are mentioned:

  • How to Sum Columns in Excel using AutoSum
  • How to Sum a Column in Excel with One Click
  • How to Enter the Sum Function Manually to Sum a Column in Excel
  • How to Use Subtotal in Excel to Sum Only Filtered Cells
  • How to Convert Tabular Data to Excel Table to Get the Sum of Column

How to Sum Columns in Excel with AutoSum

If you ever experienced using AutoSum operation, you must know how remarkable a tool it is. Using this tool lets you have the sum of an entire column. Have a look at the data given below and understand that you need the sum of the values in other column B.  

  1. From the column, choose the cell given right below the last cell that needs the sum.
  1. Click on the Formula tab.
  1. Go to the Function Library group, click on the AutoSum option.

Once you are done with the last step, you will instantly get the sum of the entire column. Apart from that, you can even use AutoSum for this function. You have to select the column in which the value is added and then press the auto-sum option given in the formula tab. Right after this function, you will get the auto-sum in the required cell.

Note: With the AutoSum function, you will get the range by default while adding all the cells in the SUM formula. If you choose the cell in which you have the sum and look at the formula, you will find that it refers to each cell given above then this in the column.

You may have to face a little issue with AutoSum and that is the wrong identification of the range when there are empty cells are given in the range. When you have an empty cell or a text value, the operation auto-sum would systematically begin from the lower cell.  

How to Sum a Column in Excel with One Click

Who does not like to have computational procedures done with one click?

In this process, you have to just click on the letter of the column together with the numbers you need to sum and look at the Excel Status bar. In the Status bar, you will get the total of the selected cells.

As this method is really quick, that’s why it does not let you copy or display numeric values.

Enter the Sum Function Manually to Sum a Column in Excel

Do you know that the Sum function can be put manually as well?

Well, you need this to make total some of the cells in the columns instead of the entire column or sometimes you need to find an address of the bigger range rather than selecting it manually.

  1. In the table, click on the desired cell in which you want to see the total of the selected cells.
  1. In the selected cell, you have to enter =sum
  1. Press the Enter key from the keyboard right after selecting the range with the numbers given for total.  
  1. Now, you need to choose the range with the numbers given for the total. Press Enter key once again.

Bonus Tip: You can add the range address manually such as =sum(B1:B2000). It will be handy when you have bigger ranges for calculation.

That’s it. After the last step, you will see the sum value on the screen and the total figure will be in the right cell. If you don’t want to highlight the range. Thus, you have to add the function manually. Remember that you have to be extra careful as the SUM function works even with the hidden and filtered values.

Use Subtotal in Excel to Sum only Filtered Cells

When you want to have a sum of the visible cells only, this operation is highly successful.

  1. At first, you need to filter the table. Click on a cell given in the datasheet. Go to the Data tab and choose the Filter icon.
  1. In the column header, arrows appear. To narrow down the data, you need to click on the arrow given right after the correct header.
  1. Uncheck the option Select All and tick off the value to filter by. Now, click OK to see the results. ‘
  1. You have to select a number range to add up and choose AutoSum under the Home tab.

By following these steps, you will get the sum value of the filtered cells only.

Convert Tabular Data to Excel Table to Get the Sum of Column

Do you know getting the sum of columns is easier when your data is in tabular form?

If your data is not in the tabular form, you have first convert it in the required form so that you can have the desired outcome.

Your data must be always in the Excel table format because it helps in lots of ways. If you want to know how you can convert the data into tabular form, let’s dig out:

Follow the steps given below to convert the data in Excel table:

  1. Choose the data to convert into an Excel table.
  2. Click the Insert tab.
  1. Click the Table icon.
  1. Ensure that the range is correct in the Create dialog box. Also, make sure that the option “My table has headers” is checked. It is needed when you have headers in the data.
  1. Click OK.   

Following the steps given above would simply convert the data into an Excel table. Once you get the data in tabular form, you can now easily manage to get the sum of each column. Below are some of the steps given to follow to get the sum value:

  1. Select a cell in the Excel table.
  2. Open the Design tab. It will show up only when you select a cell in the Excel table.
  1. In the “Table Style Options” group, you need to check the “Total Row” option.

By following the steps mentioned above, you will instantly get the sum value.

AutoSum Keyboard Shortcut

In the Formula tab, the AutoSum option is used quickly and you can get the SUM value even quicker while using a keyboard shortcut. After all, a shortcut is what you call a quick way to go.

In the shortcut option, first, you have to choose the cell in which you need the sum of the column. Here is the shortcut key:

ALT = (hold the ALT key and press the equal to key) That’s it. Whatever method you choose to apply, you will get the desired results. Keep practicing and enjoy Excel functions.

Full Excel Course Certification $7 $147Enroll Now
×

 

Hello!

We are available on WhatsApp. To Start a chat click below and we'll get back to you as soon as possible

× How can I help you?