How to Create Automatic Subtotals in Excel (Step-by-Step + Free Template)

When working with **large datasets**, manually calculating totals for each group can be tedious. That’s where Excel’s **Subtotal feature** comes in handy. It automatically calculates subtotals for grouped data and even creates collapsible sections for easy navigation.

In this guide, you’ll learn how to **create automatic subtotals in Excel** step by step. Plus, download our **two ready-to-use templates** to save time.

💡 Why Use Automatic Subtotals?

Excel subtotals allow you to:

  • Automatically calculate **sums, averages, counts, or other functions** for grouped data
  • Create **collapsible sections** for better data navigation
  • Quickly **summarize large datasets** without writing formulas manually

🛠 Step-by-Step: Create Automatic Subtotals in Excel

1. Sort Your Data

For subtotals to work properly, **sort your data** by the column you want to group (e.g., by “Category” or “Region”).
Go to:

Data → Sort

2. Use the Subtotal Feature

  1. Highlight your dataset.
  2. Go to Data → Subtotal.
  3. In the Subtotal dialog box:
    • **At each change in:** Select the column you sorted by (e.g., Category)
    • **Use function:** Select a function (e.g., Sum, Average, Count)
    • **Add subtotal to:** Select the numeric column(s) you want summarized

3. Adjust Grouping

Excel will add **subtotals and collapsible grouping levels** (left margin).
Use the **+/- buttons** to expand or collapse sections.

4. (Optional) Add Multiple Levels of Subtotals

If needed, repeat the process for another grouping (e.g., first by Region, then by Category). Uncheck **“Replace current subtotals”** to keep existing levels.

📥 Download the Free Subtotal Templates

We’ve created **two prebuilt templates** so you can practice:

  • Basic Subtotals Template: Simple dataset to practice grouping and subtotals.
  • Advanced Subtotals Template: Multi-level dataset for nested grouping and advanced reporting.

📥 Download Basic Subtotals Template
📥 Download Advanced Subtotals Template

🚀 Final Thoughts

The Subtotal feature is one of Excel’s **most powerful yet underused tools** for summarizing large datasets quickly. Whether for sales reports, inventory tracking, or regional summaries, it saves time and keeps your data organized.

💡 Also read: How to Create an Excel Dashboard

💡 Related: How to Add Drop-Down Options in Excel

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.