To add subtotals in Excel, first sort your data by the column you want to group by, then go to Data > Subtotal (in the Outline group), choose the grouping column and the function (like Sum), and click OK. Excel inserts subtotal rows and a grand total, with outline buttons to collapse or expand groups.
What Are Subtotals in Excel?
Subtotals are summary rows that Excel inserts automatically into a list or table. They let you see totals for each group of data—for example, total sales per region, average test score per class, or count of orders per product. Unlike a pivot table, subtotals appear directly in your data, making them useful for quick reports or when you need to keep the original rows visible.
How Subtotals Work: The Basics
The Subtotal feature (Data > Subtotal) works on a range of cells. It requires three things:
- Sorted data: Rows with the same value in the grouping column must be adjacent. If not sorted, Excel will create separate subtotals for each block, which is usually not what you want.
- A numeric column to summarize: You can sum, count, average, find min/max, or use other functions like product or standard deviation.
- A grouping column: The column whose values define the groups (e.g., “Region” or “Category”).
When you run it, Excel adds a row after each group with the label “Total” (or “Average”, “Count”, etc.) and inserts a grand total at the bottom. It also adds outline symbols (1, 2, 3) on the left that let you collapse to just subtotals or expand to all rows.
Step-by-Step: Adding Subtotals in Excel
Here’s a concrete example. Suppose you have sales data with columns: A = Region, B = Salesperson, C = Sales amount, rows 1 to 20 (row 1 has headers).
- Sort by the grouping column: Click any cell in column A, go to Data > Sort, and sort by Region (A to Z or Z to A). This groups all rows for each region together.
- Select any cell in your data range (e.g., A1). Excel will detect the entire range.
- Go to Data > Subtotal (in the Outline group). If you don’t see it, it’s because your data is formatted as an Excel Table—convert to a range first (right-click > Table > Convert to Range).
- In the dialog box:
- At each change in: Choose “Region”.
- Use function: Choose “Sum”.
- Add subtotal to: Check “Sales amount” (uncheck others).
- Check “Replace current subtotals” if you want to overwrite any existing ones.
- Check “Summary below data” to put subtotals under each group (default).
- Click OK. Excel inserts a subtotal row after each region and a grand total at the end.
For example, if your data has three regions (North, South, East), you’ll see rows like “North Total”, “South Total”, “East Total”, and a “Grand Total” at the bottom.
Choosing the Right Function and Options
The “Use function” dropdown offers more than Sum. Common choices:
- Sum – adds numeric values (default).
- Count – counts numeric cells (use Count Numbers for non-numeric).
- Average – mean of the group.
- Min / Max – smallest or largest value.
- Product – multiplies values (rarely used).
You can also add subtotals for multiple columns at once (e.g., sum both Sales and Profit). Excel will insert separate subtotal rows for each column, but you can combine them into one row by checking both columns in “Add subtotal to”.
The “Summary below data” option places subtotals under each group; unchecking it puts them above. Most people keep it checked. The “Page break between groups” option inserts a page break after each group, useful for printing.
Removing Subtotals and Common Problems
To remove subtotals, go to Data > Subtotal and click “Remove All”. This deletes the subtotal rows and the outline symbols, restoring your original data (but not your sorting—that stays).
Common problems and fixes:
- Subtotals appear for every row: Your data isn’t sorted. Re-sort by the grouping column.
- “Subtotal” is greyed out: Your data is in an Excel Table (Ctrl+T). Convert to a range first (right-click > Table > Convert to Range).
- Subtotals don’t update when data changes: Subtotals are static. If you edit a value, you must re-run Subtotal (or remove and re-add). Alternatively, use the SUBTOTAL function in formulas (see below).
- Blank rows in data: Excel treats blank rows as group breaks. Remove or fill blank rows before applying subtotals.
Using the SUBTOTAL Function for Dynamic Subtotals
If you want subtotals that update automatically when data changes, use the SUBTOTAL function instead of the Data > Subtotal feature. For example, with sales amounts in C2:C20, you could put this in a cell below the data:
=SUBTOTAL(9, C2:C20)
Here, 9 means SUM (101 would ignore hidden rows). The function ignores rows hidden by filters, which makes it ideal for filtered lists. You can nest SUBTOTAL inside IF or other functions, but note that SUBTOTAL ignores other SUBTOTAL calls, preventing double counting.
For a group subtotal, you’d typically use a formula like =SUMPRODUCT((A2:A20="North")*C2:C20) or a SUMIFS formula. But the Data > Subtotal feature is easier for one-off reports.
Subtotals vs. PivotTables: Which to Use?
Subtotals are quick and simple, but they alter your data layout and are static. PivotTables (Insert > PivotTable) are more flexible: they summarize data dynamically, allow drag-and-drop grouping, and update when you refresh. Use subtotals when you need a quick, printable report that shows the original rows alongside totals. Use a PivotTable when you need interactive analysis, multiple summary types, or when your data is large and you want to avoid modifying the source.
Frequently Asked Questions
Why can’t I find the Subtotal button?
The Subtotal command is in Data > Outline > Subtotal. If you don’t see it, your data might be formatted as an Excel Table—convert to a range. Also, if you’re on Excel for Mac, it’s under Data > Subtotal (or Data > Outline > Subtotal in newer versions).
Can I add subtotals to multiple columns at once?
Yes. In the Subtotal dialog, check multiple columns under “Add subtotal to”. Excel will add a subtotal row for each column, but you can combine them by checking all columns—they appear in the same row.
How do I copy only the subtotal rows?
After applying subtotals, click the outline symbol “2” to show only subtotals and the grand total. Then select the visible rows and copy. Press Alt+; to select only visible cells before copying, then paste—this avoids copying hidden rows.
Do subtotals work with filters?
The Data > Subtotal feature does not respect filters—it ignores them. If you filter your data, the subtotals remain for all rows, which can be confusing. Use the SUBTOTAL function instead, which ignores filtered-out rows.
Why does my subtotal show “Count” instead of “Sum”?
You probably chose “Count” in the “Use function” dropdown, or your data column contains text. For numbers, choose “Sum”. If the column has any text entries, Sum will ignore them, but Count will count them. Check your data types.
Can I subtotal by a column with dates or text?
Yes, any column can be the grouping column. For dates, you might want to group by month or year—sort by the date column, then use a helper column with a formula like =TEXT(A2,"yyyy-mm") to create a month grouping, then subtotal by that helper column.
Related
- Excel Customer Information Template Guide
- Excel Conditional Formatting Per Row: How To
- Excel Conditional Formatting for Duplicates: How-To
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Delete an Excel File Safely on a Windows ComputerTo delete an Excel file in Windows, close the workbook, open File Explorer, find the file, select it,…
- How to Make a Copy of a Sheet in ExcelTo copy a sheet in Excel for Microsoft 365 on Windows, right-click its sheet tab, choose Move or…
- How to Archive and Save Emails in Outlook SecurelyTo archive an email in Outlook, select it and choose Archive; Outlook moves it to the Archive folder…
- Can You Use Solver in Excel for the Web? What WorksNo: Microsoft’s Solver add-in cannot be run in Excel for the web, so you can’t set up or…
- Conditional Formatting Based on Value in Excel: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells you want to format, then choose Home >…
- Track Changes in Word 365: A Practical How-To GuideTo track edits in Word 365, open your document, go to Review > Track Changes, and choose whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.