An Excel table total row is a built-in summary row you can turn on from the Table Design tab: select a cell in the table, choose Table Design > Table Style Options > Total Row, then use the drop-down in a column’s total-row cell to choose a calculation. It usually uses Excel’s SUBTOTAL function, so totals can respond to filters instead of simply adding every record in the worksheet.
What the total row is for
An Excel table is a structured range with column headers, built-in filtering, and features that help manage data as it grows. Its total row is an optional row at the bottom of that table for summaries such as a sum, average, or count. It is not the same as a worksheet row that happens to appear beneath the data: Excel recognizes it as part of the table and provides calculation choices for each column.
For example, a sales table might have columns for Date, Product, and Sales. You could show the sum of Sales, the average sale, and a count of numeric entries in different total-row cells. Each column can have its own choice, and columns you do not need to summarize can remain blank.
The feature is useful when you want a quick summary that stays associated with a list you sort or filter. It is not a replacement for a PivotTable or a separate report when you need summaries by category, month, or other groups.
How to add or remove a total row
In Excel for Microsoft 365 on Windows, click any cell inside the table. The contextual Table Design tab appears on the ribbon. In Table Style Options, select Total Row. Excel adds a row at the bottom of the table. To remove it, clear the same checkbox. Removing the total row does not delete the table’s data.
- Click a cell in the table, rather than a cell in the surrounding worksheet.
- Open Table Design and locate Table Style Options.
- Select Total Row. If the option is unavailable, check that the selected range is an Excel table, not an ordinary formatted range.
- Click a cell in the new row under the column you want to summarize, then select a calculation from its drop-down list.
You can also add a row by clicking in the table and using the table’s options to insert a total row, but the ribbon checkbox is the clearest route. On Excel for Mac, select a table cell and look for the contextual Table Design tab and its total-row option; ribbon placement can differ slightly by release. Excel for the web also offers table design controls, though the available commands and their arrangement can vary. If you do not see the option, confirm that the range is formatted as a table and that the table itself is selected.
Choosing a calculation for each column
Click the arrow in a total-row cell to see calculation choices. The list commonly includes None, Average, Count, Count Numbers, Max, Min, Sum, StdDev, and Var. The choices are contextual to the column, and some versions may show only options relevant to its contents. You can select different calculations in different columns.
- Sum adds numeric values, making it appropriate for sales, hours, or quantities when a grand total is meaningful.
- Average gives the arithmetic mean of the eligible numeric values. It is not a weighted average; for example, averaging order prices does not account for different order quantities.
- Count counts nonblank cells, while Count Numbers counts numeric entries. If a column contains a mix of text and numbers, the two results can differ.
- Max and Min return the largest and smallest eligible values, respectively.
- StdDev and Var calculate statistical measures of spread. Use them only when those measures suit the question and the data.
For example, a table with five orders whose Sales values are 120, 80, 150, 50, and 100 has a sum of 500 and an average of 100. If the Quantity column contains 2, 1, 3, 1, and 2, its sum is 9. These are separate summaries; the total row does not automatically calculate a combined metric such as revenue per unit.
How filtering affects the result
The main reason the total row is more than a formatted bottom line is that its standard calculations use SUBTOTAL. A SUBTOTAL calculation ignores rows excluded by a table filter. If you filter a sales table to show only one region, a Sum in the total row reports the sum for the visible, filtered records. Clear the filter and the summary updates to include the records shown again.
This behavior is different from a plain SUM formula, which normally includes values in filtered-out rows. It is useful for questions such as “How much did the visible products sell?” It can also surprise someone expecting a permanent grand total across the entire dataset. Remove filters or use a separate formula if the all-record total is what you need.
SUBTOTAL also distinguishes between rows hidden by filtering and rows hidden manually. Its function numbers 1 through 11 include manually hidden rows, while 101 through 111 exclude them; both forms ignore rows removed from view by a filter. Excel’s built-in total-row choices use SUBTOTAL behavior. In practice, a manual hide can therefore affect a result differently from a filter, depending on the calculation’s function number.
What formula is behind the total row?
A total-row selection inserts a formula rather than a fixed result. For a table named SalesTable with a Sales column containing 120, 80, and 150, a sum formula using the table’s structured reference is =SUBTOTAL(109,SalesTable[Sales]). It returns 350. The number 109 specifies a sum that excludes manually hidden rows as well as filtered-out rows. A total-row formula may display with a shorter structured reference when entered inside the table, but the table-column reference identifies the same data.
You can inspect the formula by clicking the total-row cell and looking at the formula bar. You may also type or edit a formula there if you need a calculation that is not in the menu. For instance, the built-in menu does not create a distinct-count summary. A custom formula can be appropriate, but consider whether its behavior under filtering is what you want. A custom SUM formula, for example, will not automatically acquire SUBTOTAL’s filter-aware behavior.
The table’s name and column names matter in structured references. To rename a table, select it and use the Table Design tab’s Table Name box. Avoid changing a column heading without checking formulas that refer to it; Excel normally adjusts structured references when table headers change, but an invalid or ambiguous reference can still cause errors.
How the total row changes as a table changes
When you add records directly below a table, Excel commonly expands the table to include them, and the total row remains at the bottom. You can also type into the first row below the table or use the table’s resize controls to include a larger range. The summary then applies to the table’s data column, not just to the rows that were present when the total was first added.
Sorting rearranges the records but does not change which table column is summarized. Filtering changes the visible records and therefore changes the standard SUBTOTAL result. If you paste data somewhere outside the table without expanding the table, however, that data is not part of the table’s total. This is a frequent cause of totals that seem too small: check the table boundary and confirm the new rows have been included.
Formatting is separate from calculation. The total row usually has a style that distinguishes it from data rows, but changing a number format does not change the underlying value. Format a total as currency, a percentage, or a number as appropriate, and make sure the column’s data type makes sense before interpreting its summary.
Common problems and how to fix them
- The total-row option is missing. Select a cell inside the table. If the contextual tab still does not appear, the range may be an ordinary range rather than a table. Use Insert > Table to create a table from a suitable range, checking the header setting before confirming.
- The total is blank. The total-row cell may be set to None, or the formula may have been cleared. Open its drop-down and choose the intended calculation again.
- The sum is unexpectedly low. Check whether a filter is hiding records, whether all intended rows are inside the table, and whether the values are numbers rather than text that looks like numbers.
- The average or count seems wrong. Inspect blanks, text entries, and cells containing formulas that return an empty string. Count and Count Numbers treat cell contents differently, and averages use numeric values rather than treating every nonnumeric entry as a number.
- A custom formula ignores the filter. Check whether it uses SUBTOTAL or another calculation with the behavior you need. A regular SUM includes filtered-out rows.
- The value does not update as expected. Check whether Excel calculation is set to automatic and whether the changed record is actually within the table. If necessary, recalculate the workbook and inspect the formula rather than relying only on its displayed result.
Total row, SUM formula, or PivotTable?
Choose a table total row for a compact summary of one or more columns at the bottom of a working list, especially when you want the result to follow filters. Use a regular formula in a worksheet cell when you need a result in a particular location, want to include data outside the table, or want to control the exact calculation. Be explicit about whether that formula should include filtered-out rows.
Choose a PivotTable when the question involves grouping and comparing records, such as sales by month and region. A table total row gives a single summary per chosen column; it does not produce a breakdown by category. These features can work together: keep the source data in a table, use its total row for a quick visible summary, and build a PivotTable when you need a more structured analysis.
Frequently Asked Questions
Does an Excel table total row include filtered-out rows?
Its standard calculations use SUBTOTAL, which excludes rows hidden by a filter. As a result, the displayed total generally reflects the records currently visible after filtering.
Can I show a total in more than one column?
Yes. Choose a calculation separately in each column’s total-row cell. For example, select Sum for Sales and Average for Order Value, leaving other columns set to None.
Can I type my own formula in the total row?
Yes. Select the total-row cell and enter a formula. Check whether it responds to filters as intended; an ordinary SUM formula does not behave like the built-in SUBTOTAL choices.
Why is my table total different from the SUM at the bottom of the sheet?
A table’s built-in total normally responds to filters, while SUM normally includes filtered-out records. The two results can also differ if the table excludes some rows or if the column contains text, blanks, or hidden records.
Can I delete the total row without deleting the table?
Yes. Clear Total Row in Table Design > Table Style Options. This removes the summary row while leaving the table and its data intact.
Related
- Power Query Basics: Import, Clean, and Transform Data
- Append Queries in Power Query: How to Combine Tables
- Power Query Editor: What It Is, How It Works, and Why
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Filter Just for Me? Use Filter ViewsYes. In Google Sheets, use a filter view to sort or narrow rows for your own view without…
- Create a Page Break in Excel: Steps and Print TipsTo create a page break in Excel for Microsoft 365 on Windows, select the cell below and to…
- Excel: Why Can’t I Group Columns? Causes and FixesIf you can’t group columns in Excel, the usual causes are a protected worksheet, an unsuitable selection, or…
- Excel PivotChart Without a PivotTable: What Actually WorksExcel cannot create a true PivotChart without an associated PivotTable: when you insert a PivotChart, Excel creates or…
- How to Add a Shared Mailbox in Outlook for iOS AppTo add a shared mailbox in Outlook for iOS, sign in with the work or school account that…
- Clear the To Field Cache in Outlook: Step-by-Step GuideTo clear Outlook’s To field suggestions, remove an individual address with the X beside it in the suggestion…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.