To subtotal multiple rows in Excel by category, sort your data by that category, select a cell in the range, then choose Data > Outline > Subtotal. Set At each change in to the category column, choose a function such as Sum, and select the columns to total. Excel inserts a subtotal row at each group break and a grand total at the end. Prepare a single header row and make sure your data is not formatted as an Excel table first.
This guide covers grouped subtotals in Excel for Microsoft 365 on Windows, such as a total for each department or product. It also explains how to subtotal several numeric columns, add nested groups, and troubleshoot missing or incorrect totals.
What You’ll Need
- An Excel workbook open in the desktop version of Microsoft Excel. The built-in Subtotal command is not available in Excel for the web.
- A rectangular data range with one header row and no completely blank rows or columns inside it.
- A category column to group by, such as Department, Region, or Month, and at least one numeric column to summarize.
- A saved copy of the workbook if the original data is important. The command inserts rows and changes the layout of the range.
Step-by-Step
- Check the layout of your data. Each row should represent one record, with field names across the first row and values beneath them. For example, a sales list might have the headers Department, Rep, and Sales, with each transaction on a separate row. Remove merged cells and blank rows within the list; they can cause Excel to detect only part of the data or split it into separate ranges. Keep notes and unrelated totals outside the list.
- Convert an Excel table to a regular range, if necessary. Click inside the data. If a Table Design tab appears, the data is an Excel table. The Subtotal command is unavailable for tables. Choose Table Design > Convert to Range, confirm the prompt, and check that the header row and records remain in place. If you do not see Table Design, your data may already be a normal range.
- Sort by the field you want to subtotal. Click a cell in the category column, then select Data > Sort A to Z or Data > Sort Z to A. For dates or numbers, choose the relevant oldest-to-newest or smallest-to-largest sort. If Excel asks whether to expand the selection, choose to expand it so that each row’s other values stay together. Sorting is essential: Excel inserts a subtotal each time the chosen field changes from one value to another. Unsorted groups can therefore produce several partial subtotals for the same category.
- Open the Subtotal dialog. Select any cell in the data range and choose Data > Outline > Subtotal. Excel uses the current region around the selected cell, so selecting the whole range first is not usually necessary. In recent Excel for Mac desktop versions, look for Data > Subtotal; the ribbon arrangement can vary by version. Excel for the web does not offer this command, so open the workbook in desktop Excel to insert automatic subtotals.
- Choose when Excel should insert a subtotal row. In At each change in, select the category header, such as Department. This setting tells Excel to start a new subtotal whenever the value in that column changes. Choose a column that is consistently filled in; blank category cells or slightly different spellings, such as East and east, can create unexpected groups.
- Select the calculation and the columns to total. Set Use function to Sum for sales, quantities, or other values that should be added. Choose Count to count numeric entries, Average for a mean, or another listed function if it suits the data. Under Add subtotal to, check each numeric column that needs a result. For example, check both Units and Sales to get two separate subtotals for every department. Leave text fields unchecked.
- Set the remaining options and insert the subtotals. For a first set of subtotals, leave Replace current subtotals selected. Leave Summary below data selected if you want each subtotal after its group, which is the usual layout. Select Page break between groups only when you want each group to start on a new printed page. Click OK. Excel adds subtotal rows and a grand total, then displays outline controls along the left edge.
- Review the results and use the outline. Check one group by comparing its subtotal with the source rows, then confirm that the grand total is plausible. Use the numbered outline buttons at the top left to show different levels of detail: the lowest level shows the grand total, the next shows group subtotals, and the most detailed level shows all records. The plus and minus controls beside groups expand or collapse individual sections. Collapsing hides detail; it does not remove it.
- Add a second level of subtotals when needed. For example, to total sales by Department and then by Rep, sort first by Department and then Rep using Data > Sort with both fields as sort levels. Run Subtotal for Department first. Open the dialog again, choose Rep under At each change in, and select the needed numeric columns. Clear Replace current subtotals before clicking OK, or the second operation will replace the first set. Excel creates nested levels that you can expand or collapse separately.
Common Mistakes
- Forgetting to sort. Subtotal groups depend on adjacent matching values, not on all matching values anywhere in the list. Sort by the selected grouping field before inserting subtotals.
- Trying to subtotal an Excel table. Convert the table to a range first. The table’s filters and formatting may remain, but the built-in Subtotal command needs a normal range.
- Selecting the wrong change field. Choosing Rep when you meant Department creates a subtotal for every representative instead. Check the selected header before confirming.
- Replacing a previous subtotal level. When adding nested subtotals, clear Replace current subtotals. Otherwise the new operation removes the earlier subtotal rows.
- Including an existing total row as source data. If your range already has totals, remove or exclude them before applying the command. Otherwise Excel may add those totals into the new result and double-count values.
- Expecting every function to total the same way. Sum adds values; Count counts numeric entries rather than all nonblank cells. Choose the function based on what the column contains.
Tips
- Subtotal multiple measures at once. In Add subtotal to, select more than one numeric field. Excel inserts a subtotal for each selected field at every group change, plus corresponding grand totals.
- Use a multilevel sort for nested groups. Set the primary sort to the outer group and the next sort to the inner group. This keeps the records in the order needed for both subtotal levels.
- Keep a clean source copy. Subtotals add rows into the data range, which can complicate later sorting, filtering, or importing. Keep an untouched sheet or duplicate the data before creating a report.
- Use the outline to focus on the summary. Collapse the detail to compare group totals, then expand a single group when you need to inspect its records. The outline is a view control, not a separate summary sheet.
- Remove generated subtotal rows cleanly. Select a cell in the range and choose Data > Outline > Subtotal > Remove All. This removes the inserted subtotal rows and outline, but does not restore a previous sort order.
Troubleshooting
The Subtotal button is unavailable. Check whether the range is an Excel table and convert it to a range. Also confirm you are using desktop Excel rather than Excel for the web, and that the worksheet is not protected in a way that prevents structural changes.
The same category has several subtotal rows. Sort by the exact field selected in At each change in. Inspect the values for extra spaces, spelling differences, inconsistent dates, or blanks. Values that look alike may not be identical to Excel.
The total is higher than expected. Look for existing totals included among the source records, duplicate records, or numeric columns that contain values you did not intend to sum. Expand the group and compare the subtotal with its detail rows. If some numbers are stored as text, convert them to numbers before calculating.
Only some columns get subtotal rows. Reopen the dialog and check the required fields under Add subtotal to. A field must contain numeric data for Sum to be meaningful. If the field is missing from the list, verify that the selected range includes its header and data cells.
You cannot see the detail or group controls. Click the outline level buttons or plus and minus controls at the left of the row numbers. If outline symbols are hidden, check Excel’s worksheet display settings for outline symbols; the subtotal rows themselves may still be present even when controls are not visible.
Frequently Asked Questions
Can I subtotal more than one column at the same time?
Yes. In the Subtotal dialog, check every numeric field you want under Add subtotal to. Excel inserts separate results for each selected column at each group break.
Can I subtotal every few rows without grouping by a category?
The built-in Subtotal command works when a value changes in a selected field; it is not designed to insert a total after an arbitrary fixed number of rows. For a fixed block size, add a helper column that labels each block, then sort or group by that label, or calculate block totals in a separate summary area.
Can I use Average or Count instead of Sum?
Yes. Choose the desired calculation in Use function. Remember that Count counts cells containing numbers, so it may not match a count of all records if some entries are text or blank.
How do I remove subtotals without deleting my records?
Select a cell in the subtotaled range and choose Data > Outline > Subtotal > Remove All. Excel removes its inserted subtotal rows and outline structure while keeping the underlying records.
Will the subtotals update automatically when I edit the data?
The displayed calculations update when values in the existing groups change, but new records may not be included if they fall outside the subtotal range. After adding records, check the range and group order, then remove and recreate the subtotals if needed.
Can I create subtotals in Excel for the web?
The Subtotal dialog is a desktop Excel feature. Open the workbook in Excel for Microsoft 365 on Windows or a supported desktop version for this workflow; you can still view the workbook’s existing data in a browser.
Related
- Goal Seek in Excel: A Practical Sample
- Goal Seek in Excel: Step-by-Step Guide to Finding a Value
- Excel Goal Seek May Not Find a Solution: What to Do
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 Percentage Calculator: Formulas and ExamplesTo use Google Sheets as a percentage calculator, enter the part and the total in cells, then divide…
- Convert a Corrupted Excel File Online: What to KnowYes, you can try converting a corrupted Excel file online, but conversion is not guaranteed to repair it:…
- Insert Time in Google Sheets: Entry, Shortcuts & FormulasTo insert a time in Google Sheets, select a cell and type a value such as 9:30 AM…
- Can You Find and Replace in OneNote? What You Can DoNo—OneNote does not provide a built-in Find and Replace command for automatically changing every occurrence of text across…
- Why Can’t I Merge Cells in Google Sheets?You usually cannot merge cells in Google Sheets because the selection is not a single, adjacent rectangular range,…
- How to Set an Out of Office Reply in Outlook 365To set an out of office reply in Outlook 365, open Automatic Replies, switch them on, write your…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.