Total the data in an Excel table

You can quickly total data in an Excel table by enabling the Total Row option, and then use one of several functions that are provided in a drop-down list for each table column. The Total Row default selections use SUBTOTAL functions, which allow you to include or ignore hidden table rows, however you can also use other functions.

1. Click anywhere inside the table.

2. Goto Table Tools > Design, and select the check box for Total Row. On a Mac go to Table > Total Row.

   

3. The Total Row is inserted at the bottom of your table.

   

   Note: If you apply formulas to a total row, then toggle the total row off and on, Excel will remember your formulas. In the previous example we had already applied the SUM function to the total row. When you apply a total row for the first time, the cells will be empty.

4. Select the column you want to total, then select an option from the drop-down list. In this case, we applied the SUM function to each column:

   

   You'll see that Excel created the following formula: =SUBTOTAL(109,[Qtr 2]). This is a SUBTOTAL function for SUM, and it is also a Structured Reference formula, which is exclusive to Excel tables. Learn more about Using structured references with Excel tables.

   You can also apply a different function to the total value, by selecting the More Functions option, or writing your own.

   Note: If you want to copy a total row formula to an adjacent cell in the total row, drag the formula across using the fill handle. This will update the column references accordingly and display the correct value. If you copy and paste a formula in the total row, it will not update the column references as you copy across, and will result in inaccurate values.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

Overview of Excel tables

Video: Create an Excel table

Create or delete an Excel table

Format an Excel table

Resize a table by adding or removing rows and columns

Filter data in a range or table

Convert a table to a range

Using structured references with Excel tables

Subtotal and total fields in a PivotTable report

Add subtotals in a PivotTable

Excel table compatibility issues

Export an Excel table to SharePoint