Course / Tables

The total row

Switch on a table’s total row, choose a different total for each column, and see the totals follow a filter. Beginner · 5 min · Total row, SUBTOTAL, Filter a table

A table can carry its own total row: one row at the bottom that belongs to the table, with a total for any column you like.

Each total is a SUBTOTAL formula, and that matters: SUBTOTAL leaves out the rows a filter hides. Filter the table to one item and the total row shows the total for that item, with no formula to change.

In Excel: Table Design, then tick Total Row (or press Ctrl+Shift+T inside the table). Click a cell of the total row and a small arrow offers Sum, Average, Count, Max and Min.

The Table button is in the toolbar above the grid. Click a cell of the data first, then the button: a panel opens beside the sheet.

Steps

  1. Click a cell of the table, click Table in the toolbar, and tick Total row in the panel.

  2. Every column can have its own total. In the Table panel, under Total row, choose Sum for Qty.

  3. Now see what SUBTOTAL is for. Open the filter button on the Item heading in B1, untick everything except Chair, and apply.

Remember

In the function reference: SUBTOTAL

Previous: Formulas that read a table by name · Next: Formulas that read another sheet