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
Click a cell of the table, click Table in the toolbar, and tick Total row in the panel.
Every column can have its own total. In the Table panel, under Total row, choose Sum for Qty.
Now see what SUBTOTAL is for. Open the filter button on the Item heading in
B1, untick everything except Chair, and apply.
Remember
- Total row adds a row of totals that belongs to the table.
- Each total is a
SUBTOTAL, so it counts only the rows a filter leaves showing. - Choose a different total for each column: Sum, Average, Count, Max, Min.
- New rows go above the total row: switch it off to type under the table, or change the table's range.
In the function reference: SUBTOTAL
Previous: Formulas that read a table by name · Next: Formulas that read another sheet