Add a Subtotal Row to an Excel Table the Right Way

Formulas & functions · Updated November 18, 2026

To add a total row to an Excel table in Microsoft 365 for Windows, click anywhere in the table, open Table Design, and check Total Row in Table Style Options. Excel adds a row at the bottom; select a cell in that row to choose a calculation such as Sum or Average. If you mean separate subtotal rows after each group, use Excel’s Subtotal command on a regular range instead: that command does not work directly on a formatted table.

Two meanings of “subtotal row”

People use “subtotal” for two different layouts. The first is one summary row at the bottom of a table, showing a total or another calculation for a column. Excel calls this the Total Row. It is useful for a table of orders, expenses, inventory, or survey results where you want a summary that stays with the table.

The second meaning is a series of group summaries inserted between records—for example, a subtotal after each department in a list of transactions. These are often called grouped subtotals. Excel’s classic Data > Subtotal command creates them, but it requires a regular range rather than an Excel table. Choosing the right meaning first avoids looking for a command that is unavailable for the current data structure.

Turn on the table’s Total Row

  1. Click a cell inside the table. This makes the contextual Table Design tab available on the Windows ribbon.
  2. On Table Design, find Table Style Options and check Total Row. A new row appears at the bottom of the table.
  3. Click a cell in the new row under the column you want to summarize. Use the drop-down arrow that appears in the cell and choose a calculation, such as Sum, Average, or Count.
  4. Repeat for other columns as needed. You can leave cells blank or choose different calculations for different fields.

The row is part of the table’s layout, but it is not an ordinary record. It sits below the table’s data and is excluded from normal table sorting and filtering as a data item. If you do not see the tab, confirm that the selected cell is actually inside the table rather than in a nearby worksheet cell. On Mac, select the table and look for the contextual table-design tab; its label and ribbon arrangement can vary slightly by Excel release. Excel for the web also provides table tools, although the controls may be arranged differently from the Windows desktop ribbon.

Choose the right calculation

The Total Row offers common summaries from a cell’s drop-down. Sum adds numeric values, Average calculates their arithmetic mean, and Min or Max returns the smallest or largest value. Count counts nonblank entries, while Count Numbers counts numeric entries. Options such as standard deviation and variance are available as well. The precise choices that make sense depend on the type of column: counting order IDs is different from summing a quantity column.

For example, a sales table might have columns named Date, Region, and Amount. Sum is appropriate for Amount, while Count may be useful for the order identifier column if each nonblank identifier represents one order. Average can answer a different question—typical order value—but it is not interchangeable with total sales. A blank Amount cell is not the same as a zero, so consider how missing values should be interpreted before relying on an average or count.

When you choose a built-in calculation, Excel inserts a SUBTOTAL formula in the Total Row. This function is designed to work with filtered lists: rows excluded by a table filter are omitted from the result. The drop-down is a convenient way to create the formula without typing it, and you can inspect the formula bar if you want to see what Excel inserted. The row’s calculation updates as table records change or filters are applied.

How SUBTOTAL responds to filters and hidden rows

One useful feature of SUBTOTAL is that it distinguishes filtered-out records from visible records. In an Excel table, filtering to one region makes the Total Row summarize only the matching rows. That is often what readers expect from a “total of what I can see.” The function also has function-number variants that determine whether manually hidden rows are included. Excel’s built-in Total Row choices generally use variants that exclude manually hidden rows too.

For a standalone formula outside the table, suppose a table named SalesTable has an Amount column with values 120, 80, and 50. With the formula entered in a worksheet cell outside the table, this returns 250 when all three records are visible:

=SUBTOTAL(109,SalesTable[Amount])

If a filter hides the row containing 80, the same formula returns 170, because 120 + 50 = 170. The table name and column name in the structured reference must match the names in your workbook. If your table is named Table1 and the field is called Sales, use those actual names instead. Using a formula outside the table avoids accidentally replacing the table’s own Total Row calculation.

Do not assume every function that looks like a total behaves this way. A plain SUM formula includes values hidden by a filter, while SUBTOTAL is intended for visible-list summaries. If hidden rows have special meaning in your workflow, check which SUBTOTAL option Excel chose before treating the result as a report total.

Insert a subtotal after each group

To create a subtotal after each department, region, or category, first decide whether you need the classic outline-style result or a separate summary table. For the classic result, the source data must be a normal range. If it is an Excel table, click inside it, choose Table Design > Convert to Range, and confirm. The values remain on the sheet, but the table features—such as structured references and automatic table expansion—are removed.

Then sort the records by the field that defines each group. For instance, sort a transaction list by Department so all records for one department are together. Select a cell in the data and choose Data > Subtotal. In the dialog, specify the group field under At each change in, choose a calculation under Use function, and select the numeric column or columns under Add subtotal to. Click OK. Excel inserts subtotal rows and creates an outline that can collapse or expand detail rows.

Sorting is essential: if records for the same department are scattered through the list, Excel treats each change in department as a new group and may create repeated subtotals. The Subtotal dialog also includes options controlling whether existing subtotals are replaced and whether a page break is added between groups. Make a copy before converting a heavily formatted table if you may need its table features later. To return to a table afterward, select the resulting range and use Insert > Table, but check the added subtotal lines carefully; they are summaries, not records, and may not belong inside a new data table.

Keep the table and make a grouped summary

Converting to a range is not the only option. If you want to preserve the original table, build a separate summary elsewhere on the sheet, or create a PivotTable. A summary formula can calculate one result for each category without inserting rows into the source data. A PivotTable is often a better choice when readers need to rearrange fields, expand or collapse groups, or compare more than one measure.

For example, assume SalesTable has Region and Amount columns, and cell A2 contains the region name West. Enter this formula in B2, outside the source table:

=SUMIFS(SalesTable[Amount],SalesTable[Region],A2)

If West has two sales of 120 and 50, the result is 170. Copy the formula down beside other region names to produce a small summary list. Because the original remains a table, new rows can be included in its structured references. This approach is especially useful when you want a clean report without inserted subtotal lines interrupting the underlying records.

A PivotTable offers another route: select a cell in the table and choose Insert > PivotTable, then place the category field in Rows and the numeric field in Values. Excel commonly summarizes numeric values by Sum, but check the value-field setting if the result shows Count instead. PivotTables are interactive and can summarize several fields, but they may need refreshing after source data changes, depending on how the workbook is used.

Common problems and fixes

  • The Total Row checkbox is missing or inactive: Select a cell inside the table. If the data is only formatted to look like a table, use Insert > Table to create a real Excel table.
  • Data > Subtotal is unavailable: The selection is probably inside an Excel table. Convert the table to a range if you need the classic inserted group rows.
  • The total looks too large after filtering: Check that the cell uses SUBTOTAL, not SUM, and confirm the filter is applied to the table you are summarizing.
  • The wrong records are grouped: Sort by the grouping field before using the Subtotal dialog. Also check for inconsistent labels, such as “East” and “East ” with a trailing space.
  • The sum is zero or incomplete: Some values may be stored as text, or the selected field may not be the one containing the amounts. Check number formats and the source values; changing a cell’s displayed format alone does not always convert text into numbers.
  • The total row disappears: Recheck Table Design > Total Row. The option is a table setting, so it can be turned off and on without deleting the data records.

Frequently Asked Questions

Can I add more than one calculation to a table’s Total Row?
Yes. Choose a separate calculation in each relevant column’s Total Row cell. For example, a table can show Sum under Amount and Count under Order ID at the same time.

Can I add subtotal rows inside an Excel table?
Not with the classic Data > Subtotal command. Convert the table to a range to insert outline-style group subtotals, or keep the table and use a separate formula summary or PivotTable.

Does the Total Row update when I filter the table?
Yes. Its built-in SUBTOTAL calculation summarizes rows visible under the current table filter. This makes it useful for checking a filtered selection without changing the source records.

Can I type my own formula in the Total Row?
Yes. Select the Total Row cell and enter a valid formula, or choose More Functions from its drop-down. Keep in mind that replacing the built-in formula with a function that includes filtered-out rows may change the result’s meaning.

What is the difference between a Total Row and a subtotal?
A Total Row is one summary row at the bottom of a table. Grouped subtotals are intermediate summaries inserted after each category or group. Excel supports them through different methods, and only the Total Row is a built-in table feature.

Which method should you use?

Use the table’s Total Row when you need one quick summary at the bottom and want it to respond to filters. Use Data > Subtotal when you specifically need separate rows after sorted groups and are comfortable converting the table to a range. Choose a separate SUMIFS summary or PivotTable when preserving the source table matters or when the output needs to be rearranged and reused. Making that choice before changing the data structure keeps the source list tidy and makes the resulting totals easier to interpret.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.