With a huge selection of functions to choose from, it can be all too easy to feel a little overwhelmed. There’s more than one way to check the sum of a column of values in Excel, for instance. If you’re unsure, here are some of the best ways to sum a column 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 needing to add that information to your spreadsheet, you can use the Excel status bar to quickly see the total of a column or any range of cells. To view the sum of a column using the Excel status bar:
Highlight the data you want to sum. To select an entire column, click on the column name. Otherwise, drag over the cells to select your range. At the bottom of the screen, you’ll see information about your selection such as the average of all your values, and the number of data points in the range. You will also see a sum of all the values in your selected cells.
How Sum a Column in Excel Using AutoSum
If you want the sum of a column to be added to your spreadsheet, in many cases you can do this most quickly by using the AutoSum functionality. This will automatically select the range of values for you. In some cases, however, such as a mix of numbers and text entries, the range it chooses may not be quite right and you’ll need to fix it manually. To sum a column using AutoSum:
Click in the cell where you want the sum to appear. This should be in the same column as the data you want to sum. In the Home toolbar, click the AutoSum button.You can also use the keyboard shortcut Alt + = (hold down Alt and press the equals sign). The cell you selected will populate with a formula including the SUM function and a range from the same column. The cells included in the range will also be highlighted. If you’re happy with the range of cells that AutoSum has selected, press Enter to apply the formula. If the range isn’t what you wanted, you can drag out a different range and press Enter.The sum of the values in your column will now appear.
How to Sum a Column in Excel Using the SUM Function
As mentioned above, if your data includes both values and text, the AutoSum function will only select the values beneath the last text value in your column. You can manually create a formula using the SUM function to select your own range and ensure that you sum all of the values in your column. To sum a column using the SUM function:
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.Type =SUM( Drag out the range of cells you want to include or click on the column name to select the entire column.Type a final closed bracket and press Enter. Your sum will now calculate. If any of the selected cells contain text, these will be ignored. All values will be included, however.
How to Sum a Column in Excel Using Named Ranges
If you’re working with large amounts of data, highlighting huge numbers of cells can become time-consuming. However, it’s possible to apply a name to a specific range of data. You can then refer to this range by name in your formulas, rather than having to select all of the data each time. To sum a column using a named range:
Highlight the range of data that you want to name.Under the Formulas menu, click Define Name. Enter a name for your range and click OK. Select the cell where you want the sum to appear, and type =SUM( followed by the name of your range and a closed bracket. For example, =SUM(sales). Press Enter and Excel will calculate the sum of your named range.
How to Use SUBTOTAL to Sum a Filtered Column
If you’re working with filtered data, the SUM function isn’t much use. That’s because even when you filter your data to only show certain values, SUM will still find that total of both the visible and hidden values. If you want to find the sum of just those values returned by your filter, you’ll need to use the SUBTOTAL function. To sum a filtered column in Excel using the SUBTOTAL function:
Highlight your cells.In the Data menu, click the Filter icon. Click the Filter arrow at the top of your column of data. Set up a filter of your choosing.
Select the cell where you want your subtotal to appear.Type =SUBTOTAL(9, then select the visible cells you want to include. The 9 is part of the function that indicates that you want to find the sum of the valuesType a final closed bracket. Press Enter.The subtotal will show the correct sum of the visible values from your filtered data. In comparison, using the SUM function gives the total of all the visible cells plus any hidden cells that came between the top and bottom cells you selected.
How to Sum a Column Using an Excel Table
Although any data in a spreadsheet is by nature in a tabular form, you can convert your data into an Excel Table. Excel Tables allow you to quickly change the style of your tables, and they can be referenced directly in formulas. You can also use the Table Design tools to quickly sum a column of data. To sum a column using an Excel Table:
Highlight the data you want to convert to an Excel Table. Under the Insert menu, click Tables. Select Table. Ensure that the range is correct, and if you included headers in your selection, make sure that My Table Has Headers is checked. Once your Excel Table is created, make sure that the Total Row option is checked in the Table Design menu. A new row is added to your table that shows the sum of the values in the last column of your table. You can click the down arrow next to this value to change this from being a sum to a wide selection of other calculations if you wish.
Becoming an Excel Pro
As the steps above show, even for something as simple as learning how to sum a column in Excel, there are numerous ways it can be achieved. Excel is an incredibly powerful application, and many of us will only scratch the surface of what it is capable of. For example, did you know that you can learn how to ring an alarm in Excel? You can also pull data from another sheet in Excel, add months to a date, make box plots or bar graphs, and much, much more. The more you learn, the more useful Excel becomes. If you have your own Excel tips, feel free to drop them in the comments below. Comment
Δ
