To format subtotal rows in Excel, first sort the source data by the field you want to group, then use Data > Subtotal to insert subtotal rows. Apply formatting to those rows manually, or use conditional formatting to highlight each subtotal row automatically. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A desktop version of Excel and a worksheet containing a single, rectangular list of data with one header row.
- A column that identifies each group, such as region, department, or product category. Sort by this field before inserting subtotals.
- A numeric column to summarize, such as sales or units. Keep the list free of completely blank rows and columns so Excel can identify it correctly.
- If you want automatic formatting, know which column will contain the subtotal formulas after you create them. The example below assumes those formulas will be in column D.
If your data is formatted as an Excel table, the Subtotal command may be unavailable. Use Table Design > Convert to Range first, and confirm the conversion. This does not delete the data, but it removes the table’s structured features.
Step-by-Step
-
Prepare and sort the list. Click a cell in the data, then use Data > Sort. In the Sort dialog, select the column you want to group by, such as Region, and choose the desired sort order. If Excel asks, select Expand the selection so each row stays together. Confirm that the first row is treated as headers. Subtotal is inserted whenever the group value changes, so sorting is essential: if the same region appears in several separated blocks, Excel creates a subtotal for each block rather than one for the whole region.
Check that each record occupies one row and that the columns have clear headings. Remove blank rows inside the list, but do not remove legitimate records with blank values in a nonessential field. Save a copy before making major structural changes, especially if the workbook is shared or contains formulas that rely on fixed row positions.
-
Insert the subtotal rows. Select a cell in the sorted list and open Data > Subtotal. In the dialog, choose the grouping column in At each change in, such as Region. In Use function, select the calculation you want, for example Sum, Count, or Average. Under Add subtotal to, select the numeric column or columns to summarize, such as Sales. Leave Summary below data selected if you want each subtotal after its group, which is the usual layout.
Select Replace current subtotals when creating a new set from scratch. Clear it if you intentionally want to preserve an existing subtotal layer and add another grouping level. Click OK. Excel adds rows containing subtotal formulas and creates an outline with level buttons beside the row numbers. If the command is disabled, check whether the list is an Excel table and convert it to a range as described above.
-
Format a few subtotal rows by hand. Find the first subtotal row, then click its row number at the left edge of the worksheet. To select other separated subtotal rows, hold Ctrl on Windows and click each additional row number. On a Mac, use Command-click instead. Avoid selecting the entire worksheet; selecting only the subtotal rows prevents your formatting from changing detail records.
With the rows selected, use Home > Bold, choose a Fill Color, and set a suitable font color. For a clear boundary, use Home > Borders and choose a top border or a double bottom border. You can also change font size slightly, but keep the subtotal rows visually related to the rest of the report. If you have only a few groups, this manual approach is quick and easy to review.
-
Highlight subtotal rows automatically with conditional formatting. This is useful when the report has many groups or when you expect to refresh or rebuild it. Suppose your data and inserted subtotal rows occupy A2:D100, and the subtotal formulas are in column D. Select A2:D100, then choose Home > Conditional Formatting > New Rule. Choose Use a formula to determine which cells to format.
With subtotal formulas in D and the first selected row at row 2, enter this rule formula:
=IFERROR(ISNUMBER(SEARCH("SUBTOTAL(",FORMULATEXT($D2))),FALSE)Click Format, choose a fill, font, or border, and click OK to save the rule. Confirm that the Applies to range covers the full data area, not just column D. The dollar sign locks the check to column D while the row number changes for each row. FORMULATEXT reads the formula in D; SEARCH checks for the word SUBTOTAL; IFERROR makes ordinary detail rows, which do not contain formulas in D, evaluate as not highlighted.
This rule assumes the inserted subtotal formulas are in D and the first row in the applied range is row 2. If your subtotal values are in another column, replace D with that column. If your first data row differs, change the 2 in the reference to the first row of the Applies to range. The formula is intended for subtotal rows created with Excel’s Subtotal command, not manually typed totals or other kinds of summary formulas.
-
Make subtotal figures easy to read. Formatting the row does not change the number format. Select the subtotal value cells and use Home > Number to choose an appropriate format, such as Currency, Accounting, Number, or Percentage. Use the same decimal places and units as the detail values unless there is a good reason to distinguish them. A subtotal shown with two decimal places beside detail values rounded to whole numbers can look inconsistent, even when the underlying calculation is correct.
For a manually selected row, you can apply number formatting to the summary cells without changing the label or other columns. If you use conditional formatting, remember that it changes appearance, not the number format itself. Set number formats separately where needed. Check that columns are wide enough to show the formatted amounts and that negative values are displayed in a way readers can recognize.
-
Review the outline and the grand total. Use the outline buttons, usually numbered 1, 2, and 3 beside the row numbers, to check the result. Level 1 generally shows only the overall total, level 2 shows group subtotals and the overall total, and level 3 shows the detail rows as well. Click the level 3 button to inspect the full report, then collapse and expand a group to verify that its subtotal stays with the correct records.
Format the overall grand total separately if it needs stronger emphasis. The built-in Subtotal command normally adds a grand total at the bottom; it may not match a conditional-formatting rule that looks for the text SUBTOTAL in formulas, because the overall total can use a different formula. Check that total explicitly and apply a heavier border or bold formatting if appropriate.
Common Mistakes
- Skipping the sort. The command groups adjacent matching values, not every identical value anywhere in the list. Sort on the grouping column first, and expand the selection when Excel prompts you.
- Trying to subtotal an Excel table. The Subtotal command is designed for a normal range. Convert the table to a range before using it, and remember that the table’s automatic features will no longer apply after conversion.
- Formatting detail rows by accident. When selecting multiple subtotal rows, click the row numbers carefully. A broad selection can make every detail row bold or change its fill, obscuring the hierarchy.
- Applying a conditional-formatting rule to one column only. If the Applies to range covers only the subtotal formula cells, the rest of each row will not be highlighted. Select the full row area of the report before creating the rule.
- Assuming a highlight means the calculation is correct. Formatting only changes how a row looks. Check the grouping field, calculation type, and included columns separately, especially when detail rows contain hidden or filtered records.
- Repeatedly adding subtotal layers unintentionally. Running the command again with Replace current subtotals selected removes the earlier subtotal setup. Clear that option only when you deliberately want nested subtotals.
Tips
- Use a restrained style: one light fill, bold labels, and a clear top border are often easier to scan than several bright colors. Reserve a stronger style for the grand total so readers can distinguish it from group totals.
- Keep subtotal labels descriptive. Labels such as East Total or Support Total make a collapsed outline understandable even when the detail rows are hidden.
- After adding or deleting groups, check the conditional-formatting rule’s Applies to range. A new subtotal row outside that range will not receive the automatic style.
- Make a reusable copy of the worksheet with the chosen colors and number formats. For each new report, sort the records, create the subtotals, and review the rule range rather than rebuilding the visual design from scratch.
- For printouts, check Page Layout settings and print preview after collapsing or expanding the outline. A report that is readable on screen may break across pages in an awkward place.
Troubleshooting
The Subtotal button is unavailable. Click inside the data rather than selecting a chart or shape. If the data is an Excel table, select a cell in it and use Table Design > Convert to Range, confirm, and try Data > Subtotal again. If you are using Excel for the web, the desktop Subtotal command and outline tools may not be available for creating this layout; open the workbook in the Excel desktop app to use these steps.
Excel inserted too many subtotal rows. The grouping field may not have been sorted, or values that look identical may contain extra spaces or different spellings. Sort the field again and inspect inconsistent labels. To remove the inserted rows and restart, use Data > Subtotal > Remove All, then sort and create the subtotals again. This removes the subtotal structure, so make sure the workbook does not contain other subtotal levels you need to keep.
The conditional-formatting rule highlights nothing. Confirm that the formula column actually contains formulas generated by the Subtotal command, that the rule’s Applies to range includes the subtotal rows, and that the cell reference points to the formula column. The formula’s row number must match the first row in the applied range. Open Home > Conditional Formatting > Manage Rules to review or edit the rule.
Only the formula cell is colored, or the wrong rows are colored. Edit the rule and extend Applies to across all report columns. If ordinary formulas in the chosen check column also contain the word SUBTOTAL, they could be highlighted too; use a column reserved for the Subtotal results, or format the rows manually. If the subtotal formula is in a different column, change the column reference in the rule formula.
The totals change when you filter the list. Excel’s Subtotal command uses the SUBTOTAL function, whose behavior can depend on the selected function number and hidden rows. Check whether you chose Sum, Count, or another operation, and whether your filter is hiding records. Do not assume that a filtered view and an unfiltered report should show the same total.
Frequently Asked Questions
Can I format subtotal rows without changing the detail rows?
Yes. Select only the subtotal row headers and apply formatting, or use conditional formatting across the report range with a rule that checks the subtotal formula column. A fill, bold font, and top border are common choices.
Can Excel format new subtotal rows automatically?
A conditional-formatting rule can format rows that match its formula, provided the new rows fall within its Applies to range. If you add groups beyond that range, edit the rule to include them. A manually formatted selection does not automatically include later rows.
Why is the Subtotal command grayed out?
The most common reason is that the data is formatted as an Excel table. Convert it to a range with Table Design > Convert to Range, then try the command again in the desktop version of Excel.
Can I use different formatting for subtotals and the grand total?
Yes. Apply one style to group subtotal rows and a stronger style to the overall total at the bottom. The conditional-formatting formula shown here identifies formulas containing SUBTOTAL, so check and format the grand total separately if it is not matched.
Will formatting change the subtotal calculation?
No. Font, fill, border, and number-format choices affect how cells appear, not the calculation. To change the result, use Data > Subtotal and select the intended function and columns, then verify the grouped data.
Related
- How to Get Around a Circular Reference in Excel Safely
- Circular Reference in Excel: How to Spot and Trace It
- How to Find and Clear a Circular Reference in Excel
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Workbook vs Excel Binary Workbook: Key DifferencesAn Excel workbook is the general name for a spreadsheet file; an Excel Binary Workbook is one specific…
- Create a Saved Signature in Outlook and Set It as DefaultTo create a saved signature in Outlook, open the signature settings for your Outlook version, choose New, enter…
- Outlook Distribution List: How to Create OneTo create a personal distribution list in Outlook, open People, choose the option to create a contact group…
- Can You Search in an Excel Slicer? How to Find ItemsYes. In Excel for Microsoft 365 on Windows, you can search the items shown in a slicer by…
- Open and Repair in Excel: How to Recover a WorkbookTo use Open and Repair in Excel for Microsoft 365 on Windows, go to File > Open >…
- How to Enter a Page Break in Excel and Control PrintingTo enter a manual page break in Excel for Microsoft 365 on Windows, select the row below or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.