To subtotal data in Excel, sort your list by the column you want to group, select a cell in the list, then choose Data > Subtotal and set the grouping column, calculation, and value column. Excel inserts a subtotal row at each group change, a grand total, and outline controls for hiding or showing detail. Your data needs clear column headers and should not be formatted as an Excel table.
What You’ll Need
- Excel for Microsoft 365 desktop on Windows, or Excel desktop for Mac. The classic Subtotal command is not available for creating subtotals in Excel for the web; open the workbook in the desktop app.
- A rectangular list with one header row, consistent columns, and no blank rows or columns breaking up the data.
- A column to group by, such as Region or Department, and a numeric column to summarize, such as Sales or Hours.
Step-by-Step
- Check and prepare the list. Make sure every column has a descriptive header, such as Region, Rep, and Sales, and that each record occupies one row. Remove completely blank rows inside the list and check that values in the grouping column are consistent: East and east may be treated as different group labels. If your data is an Excel table created with Insert > Table, click inside it and choose Table Design > Convert to Range, then confirm. The Subtotal command works on a normal range, not an Excel table. Converting a table to a range keeps its data and usually its formatting, but it removes table features such as automatic expansion.
- Sort by the grouping column. Click a cell in the data, then choose Data > Sort. In the Sort dialog, select the column you want to group by under Sort by, and choose the desired order, such as A to Z. If Excel asks whether to expand the selection, choose Expand the selection so entire records move together. Click OK. For a nested grouping, sort first by the outer group and then by the inner group; for example, sort by Region and then Rep. Subtotal rows are inserted only when the selected group label changes in the sorted list.
- Open the Subtotal dialog. Click a cell within the sorted list; you do not need to select every row. Go to Data > Subtotal. In the dialog, At each change in identifies the column whose distinct runs should receive a subtotal. Choose Region, for example, if you want one subtotal for each region. If the command is unavailable, check that you are using a normal range rather than an Excel table, and that the sheet is not protected.
- Choose the calculation. Under Use function, select what Excel should calculate, such as Sum, Count, Average, Max, or Min. Choose Sum for a sales total. Count counts numeric entries, while Count Numbers is not the same as counting all nonblank text and numbers; select the function that matches what you need. The function applies to every value column you select in the next setting.
- Select the values to summarize. Under Add subtotal to, check the numeric column or columns to calculate, such as Sales. Leave descriptive columns such as Region and Rep unchecked. If you select more than one value column, Excel creates a subtotal for each selected column at every group break. You can change the grouping column or calculation later by removing the subtotals and applying the command again.
- Set the subtotal options and apply them. Replace current subtotals is normally checked for a first subtotal pass. Leave Summary below data checked if you want each group’s total after its detail rows and the grand total at the bottom. Clear it only if you specifically want summary rows above detail. Page break between groups inserts page breaks for printing. Click OK to add the subtotals and outline controls.
- Read the example and check the result. Suppose your sorted list has Region in A2:A5, Rep in B2:B5, and Sales in C2:C5. Its four sales values are 120 and 80 for East, followed by 150 and 50 for West. With At each change in set to Region, Use function set to Sum, and Sales selected, Excel should show East 200, West 200, and a grand total of 400. Check the original records if your totals differ; a missing row, text-formatted number, or sort selection that excluded a column can change the result.
- Expand or collapse the detail. Use the numbered outline buttons at the upper left of the worksheet. Level 1 generally shows only the grand total, Level 2 shows group subtotals and the grand total, and Level 3 shows all detail rows as well. Click the plus or minus symbols beside groups to expand or collapse one group at a time. The outline changes what is displayed, not the underlying records, so expand the detail before editing individual entries.
The Subtotal command creates worksheet formulas in the inserted rows. For a manual check, with Region in A2:A5 and Sales in C2:C5, where the East records occupy rows 2 and 3, this formula sums those two sales values: =SUBTOTAL(9,C2:C3). It returns 200 for the example values 120 and 80. The 9 argument means Sum; SUBTOTAL also ignores other SUBTOTAL formulas inside a range, which helps prevent group totals being added again in the grand total.
Common Mistakes
- Forgetting to sort first. If identical group names are scattered through the list, Excel inserts a subtotal each time the label changes, not one combined total for every occurrence. Sort the complete data range by the grouping column before applying the command.
- Sorting only one column. This separates values from their records and can make the results incorrect. Use Data > Sort and expand the selection, or sort a properly defined table before converting it to a range.
- Leaving blank rows or columns in the data. A blank line can make Excel treat part of the list as a separate region. Remove internal blank rows and keep the list contiguous. A blank cell in a field can also create a blank group, so fill it, label it, or decide deliberately how it should be handled.
- Using the wrong value function. Sum is appropriate for amounts, while Average answers a different question. Count may count numeric entries rather than all records, and text entries in a supposed number column will not contribute to a sum. Verify the source data and the function before relying on a total.
- Applying subtotals repeatedly without checking the option. If Replace current subtotals is checked, a new pass removes the existing subtotal rows. For nested levels, apply the inner grouping first, then apply the outer grouping with Replace current subtotals cleared. Sort by both grouping fields first so the hierarchy is consistent.
- Editing while rows are hidden. Collapsed outline levels can conceal records. Expand to Level 3 before reviewing or changing individual records, and confirm that filters are not hiding additional rows.
Tips
- Give the grouping and value columns clear names before starting. In a long list, headers such as Department and Overtime Hours make the dialog choices easier to identify and the resulting report easier to read.
- Use a multi-level outline when readers need both detail and summaries. For example, sort by Region and Rep, create the Rep subtotals first, then create Region subtotals with Replace current subtotals unchecked. Review the outline levels after each pass to ensure the nesting is useful.
- Use the Level 2 outline view for a compact report of group totals, and Level 3 when checking source rows. The outline buttons are a view control, not a filter; collapsed records are still in the worksheet.
- If you need a report that updates when you add records or rearrange fields, consider a PivotTable instead. Subtotal rows are inserted into the list itself, so new records generally require you to remove and recreate the subtotals.
- Save a copy before changing a complex report. To reverse a recent change, use Ctrl+Z on Windows or Command+Z on Mac, but do not rely on undo after closing the workbook or making many other edits.
Troubleshooting
The Subtotal command is gray or missing. Check whether the selected data is an Excel table; convert it to a range using Table Design > Convert to Range. Also check whether the worksheet is protected and whether you are in Excel for the web. Open the file in Excel desktop, unprotect the sheet if you are allowed to, and try again. On Mac, the ribbon layout can differ, but look for Subtotal on the Data tab in the desktop app.
There is no subtotal for one of the groups, or a group appears more than once. Sort by the exact field chosen in At each change in. Look for trailing spaces, inconsistent spelling, blank labels, or values that look alike but are stored differently. Correct the source labels, sort again, and reapply the subtotals. If the data is filtered, clear the filter while diagnosing so you can see the full list.
The total is smaller than expected. Check that all intended records are inside the sorted range and that no numbers are stored as text. Select a suspicious value and inspect the formula bar or cell alignment; convert text numbers to numeric values before recalculating. Also confirm that the correct column is checked under Add subtotal to and that hidden or filtered rows are not affecting what you expect to see.
The grand total looks doubled or the report has several unwanted subtotal rows. The command normally ignores nested SUBTOTAL formulas in its own calculation, but manually typed totals or other formulas can still be included as source data. Remove old summary rows before applying the command. To start over cleanly, choose Data > Subtotal, click Remove All, sort the original detail records, then apply the subtotal settings again.
New rows do not appear in the right group. Subtotal groups are based on the current sorted list, not an automatically refreshed report structure. Add the records to the list, sort the whole range again, and recreate the subtotals. If you need a frequently refreshed summary, use a PivotTable and refresh it after updating its source data.
Frequently Asked Questions
Does subtotaling change or delete my original data?
It inserts summary rows and outline controls into the worksheet; it does not remove the underlying detail records. Still, save a copy before restructuring a report, particularly if it contains existing formulas or manually entered totals.
Can I subtotal by more than one column?
Yes. Sort by the grouping columns in order, create the inner-level subtotal first, then add the outer-level subtotal with Replace current subtotals unchecked. For example, subtotal Rep within Region first, then subtotal Region. Inspect the outline to confirm the resulting levels make sense.
Can I subtotal dates by month?
The Subtotal command groups matching values in the selected field; individual dates are not automatically combined into calendar months. Add a helper column that labels each date by month and year, sort by that column, and subtotal on it, or use a PivotTable and group dates by month.
Will subtotals update when I change a value?
Existing subtotal formulas recalculate when source values change. However, adding records, changing group labels, or changing the sort order may leave the inserted rows in the wrong places. Sort the full list and recreate the subtotals after structural changes.
What is the difference between Subtotal and SUBTOTAL?
Subtotal is the Data tab command that inserts grouped summary rows and outline controls. SUBTOTAL is a worksheet function used for calculations; the command writes that function into its inserted rows. You can use the function on its own, but it does not create group breaks or an outline.
Can I subtotal filtered data?
You can apply filters, but the visible result depends on the filter and the subtotal function. SUBTOTAL excludes rows hidden by a filter, while its function number determines how manually hidden rows are treated. Clear filters when you need to inspect or rebuild the complete grouped report.
Related
- Excel: How to Add Multiple Sheets to a PivotTable
- Excel 5 Student’s Book: Is It Really for Grade 5 Students?
- Example Project Timeline in Excel: Build a Clear Plan
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Remove Duplicates in Excel: Data Tab StepsIn Excel for Microsoft 365 on Windows, select the data you want to clean, then go to Data…
- Archive Emails in Outlook to a PST FileTo archive Outlook emails in a PST file, use classic Outlook for Windows: go to File > Open…
- Clean Excel Duplicates: Find, Review, and Remove ThemTo clean duplicates in Excel for Microsoft 365 on Windows, select your data, choose Data > Remove Duplicates,…
- How to Export an Excel Tab to a New WorkbookIn Excel for Microsoft 365 on Windows, right-click the worksheet tab, choose Move or Copy, select (new book)…
- Excel Conditional Formatting: Highlight Dates 1 Year LaterTo highlight a date exactly one year from today in Excel, select the date cells and create a…
- Excel Solver Simplex: When to Use It and How It WorksExcel Solver’s Simplex LP method finds the best value for a linear objective while respecting linear constraints. Choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.