To make subtotal rows in Excel, sort your data by the column you want to group, select a cell in the data, then choose Data > Subtotal. Pick the grouping column, calculation, and value columns, and select OK. Excel inserts a subtotal after each group and a grand total at the bottom. Use the desktop version of Excel for this built-in command; Excel for the web does not offer the same Subtotal tool.
What You’ll Need
- A desktop version of Microsoft Excel, such as Excel for Microsoft 365 on Windows or Mac.
- A rectangular list with one header row, one record per row, and a column containing the group names, such as department, region, or product category.
- At least one column of numeric values to summarize. Save a copy of the workbook first if the data is important, because adding or removing subtotals changes the worksheet layout.
The Subtotal command works on a normal range, not an Excel table. If your data is formatted as a table, convert it to a range before starting. The command adds rows within the selected data, so check that there is room below the list and that you are working on the intended worksheet.
Step-by-Step
-
Check and clean the list. Use a single header row with a distinct heading for each column. Make sure each record occupies one row, and remove completely blank rows or columns from the middle of the list. Avoid merged cells, which can interfere with sorting and grouping. Numeric amounts should be stored as numbers, not text; a number aligned like text or a green error indicator may need correcting before it can be totaled.
-
Convert an Excel table to a range if necessary. Click any cell in the table. On Windows, open the Table Design tab and choose Convert to Range, then confirm Yes. On Mac, select a table cell and use the Table tab or Table Design tab, depending on the Excel build, then choose Convert to Range. The colored table formatting may remain, but the table-specific behavior is removed. If you do not see a Table Design tab when clicking the data, it may already be a normal range.
-
Sort by the column that defines each group. Click inside the list and go to Data > Sort. In the Sort dialog, choose the group field under Sort by, such as Region or Department, and choose the desired order. Click OK. If Excel asks whether to expand the selection, choose Expand the selection so entire rows stay together. Sorting is essential: the Subtotal command adds a subtotal each time the value in the chosen group column changes. If the same group appears in separate parts of an unsorted list, Excel will create separate subtotals for each run.
-
Select a cell in the sorted list. You usually do not need to highlight every row; clicking any cell within the continuous range is enough for Excel to detect the data. Check that the selected cell belongs to the list, rather than a note or blank row next to it. If the list has unrelated data immediately beside it, select the intended range carefully so Excel does not include that data by mistake.
-
Open the Subtotal dialog. On Windows, go to Data > Outline > Subtotal. In Excel for Mac, look on the Data tab for Subtotal, generally in the Outline area; the exact ribbon arrangement can vary by version. If the command is unavailable, confirm that you are using desktop Excel and that the selected data is not still an Excel table. Excel for the web does not provide this same dialog.
-
Choose when Excel should add a subtotal. In At each change in, select the field you sorted by. For example, if you sorted by Department, choose Department here. The field names shown in the list come from the header row. Choosing a different field from the sort field can create confusing breaks because subtotals are inserted whenever that other field changes in the sorted order.
-
Choose the calculation and amount columns. Under Use function, choose Sum for totals, or choose another suitable option such as Average, Count, Max, or Min. Under Add subtotal to, tick the numeric columns you want summarized, such as Sales or Hours. Leave descriptive columns such as Customer or Date unticked unless you specifically need a count for them. Excel can add subtotals to more than one selected column.
-
Review the remaining options and apply. Leave Replace current subtotals selected for a first subtotal setup; this prevents older subtotal rows from stacking up if you are rebuilding the same summary. Summary below data places the subtotal after each group, which is the usual layout. You can clear it to place subtotals above groups. Select Page break between groups only if you want each group to start on a new printed page. Click OK to insert the rows.
-
Review the result and use the outline controls. Excel adds subtotal rows and a grand total, usually with outline buttons numbered 1, 2, and 3 at the left of the row numbers. Click 2 to show group totals and the grand total while hiding the detail rows; click 3 to show all rows. The minus and plus buttons beside groups collapse or expand individual sections. Check several group totals against their detail rows before relying on the summary.
For example, a sorted sales list might contain several consecutive rows for North, then South, then West. Choosing Region under At each change in and Sales under Add subtotal to produces a sales subtotal after each region, followed by an overall total. The detail records remain in the sheet; they are hidden when you collapse the outline, not deleted.
Common Mistakes
- Forgetting to sort first. Excel groups consecutive changes, not every matching value throughout the worksheet. Sort by the group field before opening the dialog.
- Sorting only one column. This separates values from the rest of their records. When Excel prompts you, expand the selection so each row moves as a unit.
- Leaving the data as an Excel table. The Subtotal command is disabled for tables. Convert the table to a range, then apply the command.
- Selecting the wrong change field. The field in At each change in should normally match the column used to sort. Otherwise, a group can be split into multiple subtotal sections.
- Including headers or unrelated cells in the wrong way. Keep one clear header row and avoid extra title rows inside the data. Check the range Excel detects before applying the operation, especially if other values touch the list.
- Expecting text to add up. Sum works on numeric values. Numbers stored as text may be ignored, so convert those entries to numbers and check for spaces or symbols imported with the values.
- Adding a second layer of subtotals without planning. The dialog’s Replace current subtotals option replaces existing subtotal rows. To create nested subtotals, clear that option and carefully choose a second grouping level; sort by the outer group first and then the inner group.
Tips
Keep an untouched copy of the source data on another sheet if you expect to reuse it. Subtotal rows are inserted into the list, which can affect later sorting, copying, and formulas. When you are done reviewing the summary, you can remove the inserted subtotal rows from Data > Outline > Subtotal by choosing Remove All. This removes the subtotal structure; it does not restore a prior sort order, so sort the remaining records again if needed.
Choose a calculation that matches what the numbers mean. Sum is useful for sales amounts or hours, while Average can help summarize a measure such as time per case. Count is useful for counting nonblank entries in a selected column. If different groups need different calculations, Excel’s Subtotal tool applies the chosen function to all selected subtotal columns in that operation; separate summaries or a PivotTable may be a better fit.
For a simple formula-based total instead of inserted group rows, use the SUBTOTAL function. If item labels are in A2:A6 and amounts are in B2:B6, enter =SUBTOTAL(9,B2:B6) in an empty cell. With amounts of 120, 250, 300, 180, and 400, the result is 1,250. The 9 argument tells SUBTOTAL to sum; filtered-out rows are excluded. This formula returns one total, however, and does not create a separate row for each group.
When printing, collapse the outline to the level you want readers to see, then check print preview. The detail rows can be left expanded for a full report or collapsed to show just subtotals. Use clear headings and, if needed, apply number formatting to the amount column before or after inserting subtotals so group totals are easy to compare.
Troubleshooting
- The Subtotal button is gray or missing: Make sure you are in the desktop Excel app, not Excel for the web, and click within a regular range. If the data is a table, use Table Design > Convert to Range. If the ribbon is simplified or narrow, widen the window or check the Data tab’s Outline area for the command.
- Excel inserts too many subtotal rows: The chosen group values may not be identical, or the records may not be sorted together. Look for trailing spaces, inconsistent spelling, or extra characters in the group column. Clean the values, sort again, and repeat the subtotal process.
- A group is split into multiple sections: Sort the entire list by the exact field selected in At each change in. If you have multiple grouping levels, sort by the outer group first, then by the inner group. Do not sort the group column alone.
- Some totals are zero or lower than expected: Check that the selected column is included under Add subtotal to, and confirm that its entries are real numbers. Test a suspicious cell by changing its number format or using Excel’s convert-to-number prompt. Also check filters: filtered-out records are excluded from SUBTOTAL results.
- The outline buttons are not visible: Look at the far left of the worksheet beside the row numbers, and make sure columns A and the row-number margin are not hidden or obscured by a horizontal scroll position. The outline controls may also be hidden by worksheet display settings; check Excel’s outline/display settings if necessary.
- You want to undo the change: Immediately after adding subtotals, Ctrl+Z on Windows or Command+Z on Mac may undo the action. If you have continued editing, use Data > Outline > Subtotal > Remove All to remove subtotal rows, then sort or restore the data as required. A saved backup is the safest way to recover the exact original layout.
Frequently Asked Questions
Can I add subtotal rows without sorting my data?
Not reliably for repeated group names. Excel inserts a subtotal whenever the selected field changes from one row to the next, so sort by that field first. Otherwise, the same category can receive several separate subtotals.
Can I subtotal more than one numeric column at once?
Yes. In the Subtotal dialog, select every appropriate field under Add subtotal to. Excel will add the chosen function for each selected column at each group break and include corresponding grand totals.
Can I use the Subtotal command on a formatted table?
No. Convert the table to a normal range first using the Table Design tab’s Convert to Range command. If you want to keep table features such as structured references and automatic expansion, consider a PivotTable or a formula-based summary instead.
How do I remove subtotal rows but keep the records?
Choose a cell in the list, open Data > Outline > Subtotal, and select Remove All. Excel removes the subtotal rows and outline, while leaving the detail records in place. The sort order may remain changed.
Can I create nested subtotals, such as department and then employee?
Yes. Sort by Department and then Employee. Add the outer subtotal first, then open the dialog again, choose Employee as the change field, and clear Replace current subtotals. Nested groupings can be harder to maintain, so check the outline levels and totals after applying them.
Will subtotals update automatically when I add more records?
They are not as automatic as a PivotTable or table-based summary. New records may not be included in existing subtotal ranges, and inserting records can disrupt the grouping. Add the records, sort the full list again, and recreate or verify the subtotals.
Related
- Excel Data Analysis: Tools, Steps, and Limits
- Excel Data Analysis ToolPak: How It Works
- Excel Environmental Data Analysis: Methods and Examples
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel: Insert a Page Break After Every Row ManuallyYes. For a few breaks, select the row immediately below the place you want to split, then choose…
- How to Unhide Rows in Excel All at Once on WindowsTo unhide every hidden row on an Excel worksheet at once, click the Select All button at the…
- Find and Replace Duplicates in Excel: Safe MethodsTo find duplicates in Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules…
- How to Edit a PivotTable in ExcelTo edit a PivotTable in Excel for Microsoft 365 on Windows, click any cell inside it, then use…
- Google Sheets Date Format: How to Turn It On and Set ItTo turn on date formatting in Google Sheets, select the cells you want to format, then choose Format…
- Conditional Formatting Rows in Excel: A Practical GuideTo conditionally format an entire row in Excel, select the rows and columns you want formatted, choose Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.