How to Add Subtotal Rows in Excel by Category or Date

How-to basics · Updated November 18, 2026

To add subtotal rows in Excel, sort your data by the field you want to group, select a cell in the list, then choose Data > Subtotal. Set the grouping field, calculation, and value column, and select OK. The command inserts a subtotal whenever the group changes, plus a grand total and outline controls for collapsing detail rows. Use a regular range rather than an Excel table.

What You’ll Need

  • A desktop version of Excel, such as Microsoft Excel for Microsoft 365 on Windows. The Subtotal command is available in desktop Excel; it is not available in Excel for the web. On a Mac, look for Subtotal on the Data tab; the ribbon layout can differ.
  • A rectangular list with one header row and a column for the group, such as Region, Category, or Date. Include the values you want to summarize, such as sales or hours.
  • A way to sort the list by the grouping column. If the data is currently an Excel table, convert it to a range first.
  • A saved copy of the workbook if the original layout or data is important. Subtotal inserts rows and changes the worksheet structure.

Step-by-Step

  1. Check the layout. Make sure each column has a clear header and that every record occupies one row. Remove completely blank rows and columns from the middle of the list; they can cause Excel to identify only part of your data. Keep the header row at the top, and avoid merged cells in the list.
  2. Convert a table to a range if necessary. Click a cell in the table. On Windows, open Table Design > Convert to Range, then confirm with Yes. The data stays in the worksheet, but the table features are removed. The Subtotal command is disabled while the selected data is an Excel table.
  3. Sort by the field that defines each group. Click inside the list and choose Data > Sort. Select the grouping column under Sort by, then choose the order you need, such as A to Z or Oldest to Newest, and select OK. Sorting keeps matching group values together so Excel can recognize where one group ends and another begins. If you need to preserve the original order, copy the data to another sheet first.
  4. Select a cell in the list. Click any cell within the data, rather than selecting just the value column. Excel can usually detect the full contiguous list from that cell. Check that the first row contains headers; this helps Excel use the correct field names in the next dialog.
  5. Open the Subtotal dialog. Choose Data > Subtotal. In the dialog, use At each change in to select the column you sorted by. For example, choose Region to create one subtotal each time the region changes. The list of fields is based on your headers.
  6. Choose the calculation and values. Under Use function, select the calculation you want, such as Sum, Count, or Average. Under Add subtotal to, tick each numeric column that should be summarized. For a sales report, you might choose Sum and tick Sales, while leaving fields such as Region and Product unticked. You can select more than one value column.
  7. Set the row and replacement options. Replace current subtotals is normally appropriate for the first pass. Summary below data places each subtotal after its group and the grand total at the bottom; leave it selected for the usual report layout. Page break between groups is optional and is useful when printing each group on a new page. Select OK to insert the rows.
  8. Review the result and use the outline. Excel adds subtotal rows and displays outline controls beside the row numbers. Select outline level 2 to see group totals with the details collapsed, or level 1 to show only the grand total. Select level 3, or the plus buttons beside groups, to expand the underlying records. Check the displayed totals against a few groups before sharing the report.

For example, if a sorted sales list has Region and Sales columns, choosing Region, Sum, and Sales creates a sum after each region. The values remain in the detail rows; Excel adds subtotal formulas in new rows. If you edit a sales value later, the subtotal formulas recalculate. If you add or remove records, check the range and subtotal placement again rather than assuming the report structure will update in exactly the way you expect.

If your records are grouped by both Region and Salesperson, sort by both fields in that order. You can create nested subtotals by applying the Subtotal command again for another grouping field and clearing Replace current subtotals on the later pass. This preserves the existing subtotal rows while Excel adds the additional level. Review the outline afterward to confirm that the levels and totals are useful for your report.

Common Mistakes

  • Not sorting first. Subtotal works at each change in the selected field, not by searching the entire sheet for matching values. If the same category appears in several separated blocks, Excel creates a subtotal for each block. Sort by the grouping field before opening the dialog.
  • Trying to subtotal an Excel table. The command is unavailable for a table. Convert it to a range as described above, or use table formulas and a separate summary instead if you need to keep table features such as automatic expansion.
  • Selecting the wrong field or measure. At each change in controls where Excel inserts rows; Add subtotal to controls which columns are calculated. Choosing Sales in the first list or a text field in the second can produce an incorrect or unhelpful result. Read both lists before confirming.
  • Replacing existing subtotals by accident. When adding a second level, leaving Replace current subtotals selected removes the previous subtotal setup. Clear that option to retain the first level.
  • Sorting only one column. This can detach names, dates, or quantities from their original records. Sort the entire data list, and when Excel asks, choose to expand the selection.

Tips

  • Pick the right function. Sum is typical for sales or hours, Count counts numeric entries, and Average calculates a mean. Count Numbers does not count text entries, so choose the function that matches the data you want to measure.
  • Use dates intentionally. Sorting by a date column creates a subtotal for each distinct date, not automatically for each month. To summarize by month, add a Month or Year-Month helper column, fill it for every record, sort by it, and use it as the grouping field.
  • Keep a detail view available. Outline levels let you switch between a compact summary and the underlying records without deleting anything. Use the plus and minus controls to open a single group when you need to inspect its entries.
  • Use a manual SUBTOTAL formula when you do not want inserted rows. With amounts in D2:D6 containing 12, 18, 10, 20, and 15, enter =SUBTOTAL(9,D2:D6) in an empty cell. It returns 75. The function number 9 means Sum, and SUBTOTAL ignores filtered-out rows. Unlike the Subtotal command, this formula does not insert a row after every group.
  • Remove the structure cleanly. To remove inserted subtotals, choose Data > Subtotal > Remove All. This removes the subtotal rows and outline, but leaves the underlying records. Save a copy first if you want to preserve the report layout.

Troubleshooting

  • Subtotal is grayed out. Check whether the data is formatted as an Excel table. Click inside it and use Table Design > Convert to Range, then try again. Also confirm that you are using desktop Excel: the command is not available in Excel for the web. On Mac, check the Data tab for the command; its position can vary by ribbon layout.
  • Excel inserts several totals for one category. The group values are probably not identical or are not together. Sort the full list by that column. Look for spelling differences, extra spaces, or values that look alike but differ, such as text stored differently from numbers.
  • The total is zero or missing values. Check that the selected Add subtotal to field contains numbers, not numbers stored as text. Confirm that you chose the correct field and calculation. If the cells contain formulas, check that those formulas return numeric results.
  • Records appear to have moved away from their details. Undo immediately with Ctrl+Z on Windows, then repeat the sort and choose Expand the selection if Excel asks. On Mac, use Command+Z. Never sort a single column of a multi-column record by itself.
  • Existing subtotal rows disappeared. Run the command again with Replace current subtotals cleared when adding another level. If the result is too complicated, choose Data > Subtotal > Remove All and rebuild the subtotals from the sorted detail list.

Frequently Asked Questions

Can I add subtotal rows without sorting?
Not reliably with the Subtotal command. It inserts a subtotal when the value in the chosen grouping field changes from one row to the next, so sort the entire list by that field first.

Can I subtotal more than one column?
Yes. In Add subtotal to, select every value column you want calculated. Excel inserts the same chosen function for each selected column. To use different functions for different measures, apply subtotals in separate passes and review the replacement option carefully.

Can I use Subtotal with filtered data?
You can filter the list, and Excel’s SUBTOTAL calculations generally exclude rows hidden by a filter. The inserted subtotal layout is still based on the sorted groups in the list, so clear or adjust filters if you need to review all records.

Will subtotal rows update when I change a value?
The subtotal formulas recalculate when values in their ranges change. If you add records, remove rows, or change the grouping field, review the sorted list and subtotal rows; you may need to remove and recreate the subtotals.

How do I show only the grand total?
Select outline level 1 beside the row numbers. To show each group’s subtotal but hide its detail rows, select level 2. Choose a higher level or expand the plus controls to reveal more detail.

Can I subtotal by month instead of by individual date?
Yes. Add a helper column that labels each record with its month and year, such as November 2026, then sort and subtotal using that column. Including the year avoids combining the same month from different years.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.