Skip to main content

How to Add a Total Row to an Excel Table

· 6 min read
Jagdish Kumawat
Founder @ Dewiride

An Excel table can total itself. Tick Total Row on the Table Design tab and a Total row appears under the data, with a drop-down in each of its cells that picks Sum, Average, Count and more, no formula typing needed.

The example is a small table of fruit and quantities: Apple 12, Banana 8 and Cherry 30, in A1:B4 of a sheet in Excel for the web. To make a range a table, see How to Format Data as a Table in Excel.

Step 1: Tick Total Row​

Select any cell in the table. The Table Design tab appears on the ribbon. On it, tick Total Row:

The Table Design tab with the table name Table2 on the left and the Total Row check box, still empty, outlined in red among the Table Style Options

Step 2: Check the Total​

A Total row appears under the last row of data. Here it's row 5, and B5 already holds the sum of the Quantity column, 50:

The fruit table with a Total row in row 5: Total in A5, and B5 selected showing 50 in bold, with =SUBTOTAL(109,[Quantity]) in the formula bar and a drop-down arrow next to the cell

The formula bar shows what Excel wrote: =SUBTOTAL(109,[Quantity]). SUBTOTAL with 109 means sum, leaving out hidden rows, and [Quantity] is the table's Quantity column (How to Use Structured References in an Excel Table). The codes are in How to Total Only Visible Rows in Excel with SUBTOTAL.

Microsoft's Total the data in an Excel table says When you apply a total row for the first time, the cells will be empty. In my table, Excel for the web filled in the sum of the number column straight away. Either way, the next step chooses what each cell shows.

Step 3: Choose a Different Function​

Select the total cell, here B5, and the drop-down arrow next to it. The list has None, Average, Count, Count Numbers, Max, Min, StdDev, Sum, Var and More Functions.... I picked Average:

B5 selected with its drop-down open, listing None, Average, Count, Count Numbers, Max, Min, StdDev, Sum, Var and More Functions..., with Average outlined in red

B5 now shows the average, 16.6666667, and the formula bar shows =SUBTOTAL(101,B2:B4). 101 is average, leaving out hidden rows:

B5 showing 16.6666667 with =SUBTOTAL(101,B2) in the formula bar

Two things I noticed here:

  • Count isn't a sum. Count counts the cells that aren't empty; it doesn't add them up. When a total shows how many rows there are instead of their total, Count is the reason (Fixed: Excel Table Total Row Counts Instead of Adding).
  • The drop-down wrote cells, not the column name. Microsoft's article shows the drop-down writing =SUBTOTAL(109,[Midwest]), with the column's name. Excel for the web wrote B2:B4. The next step checks that the total still keeps up when the table grows.

Step 4: Add a Row Above the Total​

The total row always stays at the bottom of the table. To add a row of data, select a cell in the Total row, then on the Home tab select the arrow under Insert and Insert Table Rows Above. I typed Date and 6 into the new row 5:

The table with a new row 5, Date 6, and the Total row moved down to row 6, where B6 shows 14 and the formula bar shows =SUBTOTAL(101,B2)

The total moved to row 6 and its range grew to B2:B5 by itself, so the average of 12, 8, 30 and 6 is 14.

Common Mistakes​

MistakeWhat happensDo this instead
Picking Count for a column of numbersThe cell shows how many values there are, not their totalPick Sum
Copying a total formula and pasting it in the next total cellMicrosoft's article says the column references don't change, so the result is wrongDrag it across with the fill handle, or pick the function from the cell's own drop-down
Expecting a hidden row to count in the totalThe drop-down's codes, 101 to 111, leave out rows hidden with Hide RowUnhide the row, or type a SUBTOTAL with a code from 1 to 11

Troubleshooting​

SymptomLikely causeFix
Table Design isn't on the ribbonThe selected cell is outside the tableSelect a cell inside the table
The total is a count of the rowsCount was pickedPick Sum (Fixed: Excel Table Total Row Counts Instead of Adding)
The total doesn't drop after hiding a row in Excel for the webThe formula wasn't recalculatedFixed: SUBTOTAL 109 Still Includes a Hidden Row in Excel for the Web
Filtered-out rows aren't in the totalSUBTOTAL always leaves out rows a filter hidesClear the filter to total every row

FAQ​

Can I turn the total row off again?​

Yes: clear Total Row on Table Design. Microsoft's article says that if you turn it back on later, Excel remembers the formulas you chose.

Can I use my own formula in a total cell?​

Yes. Pick More Functions... in the drop-down, or type any formula into the cell.

Can other columns have totals too?​

Yes. Every cell in the total row has its own drop-down, so the first column can stay as the word Total while each number column gets its own function.

Conclusion​

Select a cell in the table, then Table Design → Total Row. Each total cell's drop-down picks the function, written as a SUBTOTAL that leaves out hidden and filtered rows, and the total stays at the bottom as rows are added above it.

Additional Resources​