How to sum columns in Excel

How to quickly view the sum of a column in Excel

If you just want to know the sum of a column without adding that information to the spreadsheet, you can use the Excel status bar to quickly view a column or any cell The sum of the grid areas.

To see the sum of a column using the Excel status bar:

1. Highlight the data you want to summarize. To select an entire column, click the column name. Otherwise, drag cells to select your range.
   
2. At the bottom of the screen, you'll see information about your selection, such as the average of all values ​​and the number of data points in the range. You will also see the sum of all values ​​in the selected cells.
   

How to use AutoSum to sum columns in Excel

If you wish to add the sum of a column to a spreadsheet, in many cases , you can do this fastest by using the AutoSum function. This will automatically select a range of values ​​for you.

However, in some cases, such as a mix of numeric and text entries, the range it selects may not be quite right and you will need to fix it manually.

Use AutoSum to sum columns:

1. Click the cell where you want the sum to appear. This should be in the same column as the data you are asking to sum.
   
2. In the Homepage toolbar, click the AutoSum button.
   • You can also use the keyboard shortcut Alt = (hold Alt and press equal sign).
     
3. The cells you select will be populated with a formula that includes the SUM function and a range from the same column. Cells within the range will also be highlighted.
   
4. When you are satisfied with the range of cells selected by AutoSum, press Enter to apply the formula. If the range isn't what you want, you can drag out a different range and press Enter.
5. The sum of the values ​​in the column will now be displayed.
   

How to use the SUM function to sum columns in Excel

If your data contains both values ​​and text, the AutoSum function will select that column The value below the last text value in .

You can manually create a formula using the SUM function to select your own range and ensure that you sum all values ​​in the column.

Use the SUM function to sum columns:

1. Click the cell where you want the sum to appear. This can be any cell you choose - it doesn't have to be in the same column.
2. Enter =SUM(
   
3. Drag out the range of cells you want to include or click a column name to select the entire column.
4. Type Close the bracket at the end and press Enter.
   
5. Your sum will now be calculated. If any of the selected cells contain text, these will be ignored. However , all values ​​will be included.
   

How to sum columns in Excel using named ranges

If you are working with large amounts of data, highlight Large numbers of cells can be time-consuming. However, it is possible to apply names to specific ranges of data. You can simply reference the range in your formulas by naming it without having to select all the data each time.

Sum columns using named ranges:

1. Highlight the data range to be named.
2. Under the Formula menu, click Define Name.
   
3. Enter a name for your range and click OK.
   
4. Select the cell where you want the sum to appear and type =SUM( followed by the range name and the closing parenthesis. For example, =SUM(sales).
   
5. Press Enter key and Excel will calculate the sum of your named range.

How to use SUBTOTAL to sum a filtered column

The SUM function is not of much use if you are working with filtered data. Even if you use a data filter to display only certain values, the SUM function calculates the sum of all visible and hidden values.

If you only want to find the sum of those values ​​returned by the filter, you need to use the SUBTOTAL function.

To use the SUBTOTAL function to filter a column in Excel Sum:

1. Highlight your cells.
2. In the Data menu, click the Filter icon .
   
3. Click the filter arrow at the top of the data column.
   
4. Set the filter of your choice.
   
   
5. Select the cells where you want the subtotal to appear.
6. Type =SUBTOTAL(9,Then select the visible Cell . 9 is part of the function and means you are looking for the sum of the values ​​
7. Type the final closing bracket.
   
8. Press Enter.
9. Subtotal will show the correct sum of the visible values ​​in the filtered data. In contrast, using the SUM function gives you the sum of all visible cells, plus the top and bottom of your selection Any hidden cells between cells.
   

How to sum columns using an Excel table

Although the data in the spreadsheet is in tabular form stored, but you can convert the data into an Excel table. You can quickly change the styles in the Excel table, and you can also reference them directly in formulas.

You can also use the table design tool to quickly summarize a column of data .

To sum columns using an Excel table:

1. Highlight the data you want to convert to an Excel table.
   
2. Under the Insert menu, click Table.
   
3. Select Table.
   
4. Make sure the range is correct and if you include headers in the selection, make sure My table has headers is checked.
   
5. After creating the Excel table, Please make sure the Total Row option is selected in the Table Design menu.
   
6. will add a new row to the table, displaying the table The sum of the values ​​in the last column.
   
7. If you wish, you can click the down arrow next to the value to change it from a sum to a variety of other calculations.
   

Become an Excel Pro

Even something as simple as summing columns in Excel has multiple ways to do it, as the above steps illustrate. Show. Although many people have only scratched the surface of Excel's capabilities, it is still a very powerful application.

The above is the detailed content of How to sum columns in Excel. For more information, please follow other related articles on the PHP Chinese website!