This tutorial demonstrates how to add a total or subtotal row to a table in Excel.
Add a Total Row
If you have a table in Excel with numeric data, you can easily add a total row to it. For example, say you have a table with products, prices, and sales by month.
Now, add a total for Column G (Total Sales).
- Click anywhere in the table. The Table Design tab appears in the Ribbon.
- Click on Table Design.
- Then check Total Row.
A new row is added at the end of the table with the total amount of sales revenue.
You could also insert the total row with a keyboard shortcut: CTRL + SHIFT + T.
To remove totals, you need to uncheck Total Row in the Table Design tab or again use the shortcut CTRL + SHIFT + T.
Add Subtotal Row
By default, you can’t insert subtotal rows to an Excel table, but you can do it if you convert the table to a data range.
- Click anywhere in the table, then in the Ribbon, go to Table Design > Convert to Range.
It’s the same data, just in a normal range instead of an Excel table. To check this, click anywhere in the data, and notice that Table Design tab doesn’t appear in the Ribbon.
- Now you can add subtotal rows to the data. Say you want to group data and add subtotals by month (Column D).
First, click anywhere in the data. Then in the Ribbon, go to the Data tab, and in Outline click on Subtotal.
- The pop-up screen for Subtotal appears, and here you can specify how to group data. In the first section (At each change in:) set Month, as you want to add a subtotal for each month. In the Add subtotal to section, check Total Sales to sum the column (this is checked by default).
You get the subtotal for each month (Jan-2021 and Feb-2021) as well as a Grand Total for the whole column.
The data is also grouped by month now, so you can expand or collapse by month.
Remove Subtotal Rows
To remove subtotal rows, go back to the Data tab and Subtotal. In the pop-up screen, click on Remove All. This removes all groupings and subtotals.