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 drop-down lists for each totals row. A Total row can automatically calculate totals, and it keeps track of values you enter as you enter them. It can also give you averages and counts, among other options.

  1. Enter your data in the workbook, and click any single cell within the set.

  2. Click Insert > Table. The Create Table dialog box opens.

  3. Review the cells selected in the Create Table dialog box. If you have headers for each column of data, select the My table has headers checkbox.

    Create Table dialog box

    Tip: You can also use your cursor to select what cells to include in your table. With the Create Table dialog box open, click the first cell in the first column that contains your data. Drag your cursor to select your cells.

  4. Click OK to create your table.

  5. Click anywhere inside the table.

  6. Click Design, and select the check box for Total Row.

    Total Row option in Design tab
  7. The Total row is inserted at the bottom of your table.

    Total row added to the table

    Note: If you want to copy a sum to an adjacent cell in the table, drag the cell using the fill handle across or down. This will update the column references accordingly and display the correct value. Do not copy and paste a sum in the table as this will not update the column references, and will result in inaccurate values.

You can also apply a different function to the total value. To do that, click the dropdown next to the total value and select a function.

For example, you can have the total display the average value in a table containing sales data.

Select Average to apply the AVERAGE function to the table

Or lookup a value in a table.

More Functions

Insert Function dialog box

See Also

Create or delete an Excel table in the worksheet

Subtotal and total fields in a PivotTable report

Add subtotals in a PivotTable

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!