PlayInfoBox

Home /Utilities /How to Use Subtotal to Group and Sum Data in Excel

How to Use Subtotal to Group and Sum Data in Excel

Tap through each step to add automatic subtotals by group.

  1. Sort by the grouping column first

    Sort your data by the column you want to group by, such as region or department.

  2. Select the data range

    Select the entire table you want to apply subtotals to.

  3. Go to Data > Subtotal

    In the "Outline" group on the "Data" tab, click "Subtotal."

  4. Set the grouping and calculation fields

    Choose the field to group by and the field to calculate, then click OK.

  5. Why it is useful

    When you need automatic subtotals for each group, such as by region or department, you can get there in a few clicks instead of building formulas by hand.

Automatic subtotals for every group at once

When you need to break down sales by region or department and add a subtotal row after each group, Subtotal inserts every one of those rows automatically instead of requiring you to add them one at a time.

Sorting first is not optional

Subtotal groups rows based on consecutive matching values, so if rows from the same group are scattered throughout the sheet, the same group will get subtotaled multiple times. Always sort by the grouping column before running Subtotal.

Frequently Asked Questions

What happens if I do not sort the data first?

If rows belonging to the same group are not next to each other, Subtotal will calculate separate subtotals for each scattered block, so always sort by the grouping column first.

How do I remove the subtotals afterward?

Open the Subtotal dialog again and click "Remove All" to delete every inserted subtotal row and return to your original data.