Total the data in an Excel table

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 More...Less

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 the SUBTOTAL function, which allow you to include or ignore hidden table rows, however you can also use other functions.

Your browser does not support video.

  1. Click anywhere inside the table.

  2. Go to Table Tools > Design, and select the check box for Total Row.

    Total Row option in Design tab

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

    Excel table with the Total Row turned on

    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:

    Example of selecting a Total Row formula from the Total Row formula drop-down list

    You'll see that Excel created the following formula: =SUBTOTAL(109,[Midwest]). 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.

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 the SUBTOTAL function, which allow you to include or ignore hidden table rows, however you can also use other functions.

Your browser does not support video.

  1. Click anywhere inside the table.

  2. Go to Table > Total Row.

    Total Row option in Design tab

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

    Excel table with the Total Row turned on

    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:

    Example of selecting a Total Row formula from the Total Row formula drop-down list

    You'll see that Excel created the following formula: =SUBTOTAL(109,[Midwest]). 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.

You can quickly total data in an Excel table by enabling the Toggle Total Row option.

  1. Click anywhere inside the table.

  2. Click the Table Design tab > Style Options > Total Row.

    The Total row is inserted at the bottom of your table.

Set the aggregate function for a Total Row cell

Note:This is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Microsoft 365 subscribers.

The Totals Row lets you pick which aggregate function to use for each column.

Totals Row drop-down showing aggregate function options

  1. Click the cell in the Totals Row under the column you want to adjust, then click the drop-down that appears next to the cell.

  2. Select an aggregate function to use for the column. Note that you can click More Functions to see additional options.

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

See Also

Insert a total row

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

Subtotal and total fields in a PivotTable

Excel table compatibility issues

Export an Excel table to SharePoint

Need more help?