Subtotals in Excel - Step-by-Step Guide

Introduction

Subtotals help you quickly add up numbers in groups within a list. This feature automatically inserts summary rows that show totals for each group, making it easier to see parts of your data without doing math yourself.

When you have a sales list sorted by region and want to see total sales per region.

When you track expenses by category and want to know the total spent in each category.

When you have a list of students with their scores and want to find the average score per class.

When you want to quickly add totals for each month in a date-sorted list of transactions.

When you want to insert summary rows without creating separate pivot tables.

Steps

Step 1: Sort your data by the column you want to group by

- Data tab > Sort

Your data is arranged so that all items in the same group are together

💡 Sorting first is important because subtotals work on consecutive groups

Step 2: Select any cell inside your data range

- Worksheet area

Excel knows which data to use for subtotals

Step 3: Click Subtotal

- Data tab > Outline group > Subtotal button

The Subtotal dialog box opens

Step 4: Choose the column to group by in 'At each change in' dropdown

- Subtotal dialog box

Excel knows where to insert subtotal rows

Step 5: Select the function to use (Sum, Count, Average, etc.)

- Subtotal dialog box > Use function dropdown

Excel knows how to calculate the subtotal

Step 6: Check the box for the column(s) to subtotal

- Subtotal dialog box > Add subtotal to list

Excel will calculate subtotals for these columns

Step 7: Click OK

- Subtotal dialog box

Subtotal rows appear in your data showing totals for each group

Before vs After

Before

A list of sales data sorted by region with no totals, e.g., Region: East, West, East, West with sales numbers in adjacent column

After

The same list now has subtotal rows after each region group showing total sales for East and West, plus a grand total at the bottom

Settings Reference

At each change in

📍 Subtotal dialog box

Defines the column where Excel inserts subtotal rows when the value changes

Default: First column header

Use function

📍 Subtotal dialog box

Chooses the calculation type for the subtotal

Default: Sum

Add subtotal to

📍 Subtotal dialog box

Selects which columns get subtotal calculations

Default: First numeric column

Replace current subtotals

📍 Subtotal dialog box

Decides whether to remove existing subtotals before adding new ones

Default: Checked

Summary below data

📍 Subtotal dialog box

Controls if subtotal rows appear below or above each group

Default: Checked

Common Mistakes

Not sorting data before applying subtotals

Subtotals only work correctly when data is grouped together; unsorted data causes incorrect subtotal placement

Always sort your data by the column you want to subtotal before using the Subtotal feature

Trying to subtotal non-numeric columns

Subtotal calculations like Sum or Average require numbers; text columns cannot be summed

Select only numeric columns for subtotal calculations

Leaving 'Replace current subtotals' unchecked when adding new subtotals

This can cause multiple subtotal layers that clutter the data and confuse results

Keep 'Replace current subtotals' checked unless you want to add multiple subtotal levels intentionally

Summary

Subtotals automatically add summary rows to grouped data for quick totals and calculations.

Always sort your data by the grouping column before applying subtotals.

Choose the right function and columns to subtotal for accurate results.