Total the data in an excel table

Windows macOS Web



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.

1. Click anywhere inside the table.

2. Go to Table Tools > Design, and select the check box for 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,[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.

1. Click anywhere inside the table.

2. 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,[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.

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