There are two ways to add a total row. If your data is an Excel Table (select any cell and press Ctrl + T), tick Total Row on the Table Design tab, or press Ctrl + Shift + T.
A total row appears at the bottom with a total for the last column only; click any other cell in that row and pick Sum from its dropdown to total that column too. The dropdown also offers Average, Count, Max, Min and StdDev, so each column can show a different calculation.
The other route works on any data, table or not: select the empty row below your numbers and press Alt + =, which fills every cell with a SUM. Two things worth knowing. A Table’s total row uses SUBTOTAL rather than SUM, so it automatically ignores filtered-out rows and updates as you filter — the AutoSum version doesn’t.
And a Table’s total row extends itself when you add new rows, while the AutoSum version has to be redone.
In Excel, when you are working with data, you might need to have a total at the bottom to have a sum of all the values that you have in each column.
In this tutorial, we will learn to add a row with the total using two quick methods.
- Total Row in a Table.
- Total with AutoSum.
Add a Total Row in Excel in a Table
- First, select any of the cells in the data and press the keyboard shortcut Ctrl + T, and click OK to apply the table.
- Next, go to the Table Design tab (called Table on a Mac), and tick the Total Row checkbox in the Table Style Options group. You can also press Ctrl + Shift + T.
- Here you have a total row at the bottom of the table, with a total only for the last column.
- Now you need to select Sum from the drop-down in each cell of the total row to get the sum of each column.
- Once you select Sum from the drop-down for each of the columns that you have in the table, you will have a complete total row.
- Once you apply the table to the data, you can also right-click on it, go to Table, and click on Totals Row from there.
Add a Total Row with the Auto Sum
- First, select the cells in the row right after the last row of the data.
- After that, press the keyboard shortcut Alt + = (AutoSum) to add the sum function in all the cells in the row to make it a total row for the data.
Apart from this, once you select the bottom row, you can go to the Home tab, Editing group, and click on the AutoSum button to add the SUM function in all the cells.
The moment you click on the AutoSum button, you get the total values in the row selected.
How the Table Total Row Works
The total row doesn’t use SUM. When you pick Sum from the drop-down, Excel enters a SUBTOTAL formula with the function number 109, like the one below (where Sales is the column name):
=SUBTOTAL(109,[Sales])
SUBTOTAL with 109 skips the rows that you hide with a filter, so when you filter the table, the total changes to show only the visible rows. The drop-down also has Average, Count, Max, Min and more, and you can pick More Functions to use any other function.
A total row with SUM, like the one AutoSum adds, still counts the filtered rows. If you want the same filter-aware total outside a table, use SUBTOTAL yourself:
=SUBTOTAL(109,B2:B10)
Frequently Asked Questions
Why doesn’t the total row change when I add data below the table?
Because the new rows are below the total row and outside the table. Turn off the total row (Ctrl + Shift + T), add the data so the table grows, and turn it back on. Or right-click a row inside the table and use Insert ➜ Table Rows Above.
Can I show a count or an average instead of a sum?
Yes. Click the cell in the total row and pick Count, Average or any other option from the drop-down. Each column can have a different function.
Why does AutoSum leave out some cells?
AutoSum stops at the first blank cell or text cell above it. Check the range it highlights before you press Enter, and change it if needed.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.