What subtotals do and when you need them

Subtotals in Excel automatically insert summary rows that add up, average, or count groups of data in your spreadsheet. When you use the Subtotals feature, Excel looks at a column you choose (like a product category or region), groups the rows that match, and inserts a row after each group showing the total. This saves you from writing formulas by hand and keeps the groups visually organized.

You need subtotals when your data naturally breaks into categories and you want to see a summary for each one. A sales spreadsheet grouped by region, a budget broken down by department, or an inventory list organized by supplier are all good candidates. The feature works fastest when your data is already sorted by the grouping column — if it is not, Excel will sort it for you.

Subtotals are different from a pivot table, which is better if you want to rearrange and filter groups on the fly. Subtotals are better if your data is already in the order you want and you just need quick summary rows inserted.

Key Takeaways

  • Your data must be sorted by the column you want to group by before you insert subtotals, or Excel will sort it for you.
  • The Subtotals feature lives in the Data menu and works on a selected range, so highlight your data first.
  • You choose which column to group by, which column to summarize, and what calculation to use (sum, average, count, and others).
  • Excel adds outline buttons on the left side of your sheet so you can collapse and expand groups to see only the summaries or all the detail.
  • You can remove subtotals by going back to the Data menu and choosing Remove All, which leaves your original data untouched.

Prepare your data before adding subtotals

Before you open the Subtotals dialog, sort your data by the column you want to group by. If your spreadsheet has a header row (the first row with column names), keep it at the top — do not include it in the sort. Select the data rows only, click the Data menu, and choose Sort. Pick the column you want to sort by and click OK.

For example, if you have a sales list with columns for Region, Product, and Amount, sort by Region first. All the rows for the East region will sit together, then all the West rows, and so on. Excel's Subtotals feature will then recognize these groups and insert a summary row after each one.

If your data is already sorted the way you want it, you can skip this step. But if rows are scattered — East, West, East, West — the subtotals will not group correctly, so take the time to sort first.

Select your data and open the Subtotals dialog

Click on any cell inside your data range. You do not have to select the entire range; Excel will find the boundaries automatically. Then click the Data menu at the top of the screen. Look for the word "Subtotals" in the menu — it is usually near the bottom, sometimes grouped with other data tools. Click it.

A dialog box will open with three main dropdowns. The first one says "Group by" and shows the column names from your spreadsheet. The second says "Use function" and defaults to Sum. The third says "Add subtotal to" and lists the columns you can summarize. These three choices control what your subtotals will do.

Choose which column to group by and what to calculate

In the "Group by" dropdown, pick the column that contains your categories. If you sorted by Region, choose Region. This tells Excel to insert a subtotal row every time the value in that column changes.

In the "Use function" dropdown, choose what calculation you want. Sum adds up the numbers. Average finds the mean. Count counts how many rows are in each group. Max and Min find the highest and lowest values. Most of the time you will use Sum, but the others are there if you need them.

In the "Add subtotal to" section, check the box next to the column you want to summarize. If you want to sum the Amount column, check Amount. You can check more than one column if you want subtotals for multiple columns at the same time. Leave unchecked any columns you do not want summarized.

Review the options and click OK

Before you click OK, look at the bottom of the dialog. There is usually a checkbox that says "Replace existing subtotals" — leave this checked if you are adding subtotals for the first time. There may also be a checkbox for "Summary below data" — this controls whether the subtotal rows appear after each group (checked) or before it (unchecked). Most people leave it checked so the subtotal sits below the group it summarizes.

Once you have set Group by, Use function, and Add subtotal to the way you want, click OK. Excel will insert subtotal rows and add outline buttons on the left side of your sheet.

Understand the outline buttons and collapse groups

After Excel inserts subtotals, you will see numbered buttons (usually 1, 2, and 3) on the left edge of your spreadsheet, above the row numbers. These are outline controls. Click button 1 to see only the grand total at the bottom. Click button 2 to see each group's subtotal but not the individual rows. Click button 3 to see all the detail again.

You can also click the small minus sign next to any individual group to collapse just that group and hide its rows. Click the plus sign to expand it again. This is useful when you have many groups and want to focus on one or two at a time.

The outline buttons stay in place until you remove the subtotals. They do not print by default, so if you print your sheet, the reader will see all the rows and subtotals but not the collapse buttons.

Remove subtotals when you no longer need them

If you want to go back to your original data without the subtotal rows, click any cell in your data range, open the Data menu again, and click Subtotals. The dialog will open with your previous settings still showing. At the bottom, click the button that says "Remove All". Excel will delete all the subtotal rows and outline buttons and return your data to its original state. Your data itself is not changed — only the subtotals are removed.

If you want to change the subtotals instead of removing them (for example, to group by a different column or use a different calculation), you can open the Subtotals dialog again and adjust the settings. Click "Replace existing subtotals" to overwrite the old ones with new ones.

Frequently Asked Questions

What if my data has blank rows or cells?

Blank rows can confuse the Subtotals feature because Excel treats them as group breaks. Before you add subtotals, delete any completely blank rows. If individual cells are blank (like a missing value in one row), that is usually fine — Excel will include that row in the group above it.

Can I use subtotals on more than one column at the same time?

You can group by only one column, but you can summarize multiple columns. For example, you can group by Region and sum both the Amount and Quantity columns in the same operation. Check both columns in the "Add subtotal to" section.

Do subtotals work if my data is not sorted?

No. If your data is not sorted by the grouping column, the subtotals will not group correctly. Excel will sort it for you when you click OK, but it is safer to sort it yourself first so you can see what is happening.

Can I add subtotals to text columns like product names?

You can group by a text column, but you cannot sum or average text. You can use Count to see how many rows are in each group, or Max and Min if the text is sortable (like alphabetically). For most text columns, Count is the only useful calculation.

What happens to my formulas if I add subtotals?

Subtotals are inserted as new rows, so they do not overwrite your existing formulas. However, if you have a formula that sums a range, adding subtotals will change which rows are included. It is usually safer to add subtotals first, then build other formulas around them.