Instantly Summerize Data with Excel's Subtotal Feature 

When working with long sales, inventory, or other financial lists, one might want to have some key totals at hand for quick reference to save time and avoid errors. This is where the Subtotal feature comes into play, a built-in tool in Microsoft Excel that inserts grouped summaries with just a few clicks.

This feature, whether for department-wise expenses or sales-wise regions, helps generate easy-to-read summaries without writing even one formula.


📌 What Is the Subtotal Feature in Excel?

The Subtotal feature allows automatic calculation of summaries (sum, average, count, etc.) at each group in a sorted list.

Compared to the SUBTOTAL() formula, the Subtotal tool produces an expandable view with outline buttons for collapsing and expanding grouped data. 


🧭 How to Use Subtotal in Excel – Step-by-Step

Step 1: Sort your data

Before you apply subtotals, be sure to sort your list based on the category that you want to group by (Region, Department, etc.)

Step 2: Data Tab -> Subtotal

Go to the Data tab, and then click Subtotal in the Outline group. 

Step 3: Choose Your Grouping and Calculation

In the Subtotal dialogue box:

  • At each change in: select the column to group by
  • Use function: select the summary operation (SUM, AVERAGE, COUNT, etc.)
  • Add subtotal to: choose the numeric column(s) to summarise

Click OK to apply.


🔄 The example above attempts to show subtotaling by department.

Department 
Expense
HR 
500
HR
750
Sales 
1000
Sales 
900

When Subtotal is applied:

  • The table is split by departments.
  • A blank line is added, showing the subtotal for each group (e.g., HR Total = 1250)
  • An overall Total is also added at the bottom.

Use the +/-button  on the margin to the left of each group to collapse or expand it.


💡 Pro Tips

  • If there are subtotals applied, remove them first: Data -> Subtotal -> Remove All
  • Combine with filtering for best results.
  • Apply several levels of subtotals by repeating the process (e.g. first by Region and then by Product).
  • Use Outline view (the 1-2-3 buttons on the left) for quick navigation of summaries.

🔍 Subtotal Functions

  • SUM
  • AVERAGE
  • COUNT
  • MAX
  • MIN
  • PRODUCT
  • STDEV…and more

Just a few clicks help Subtotal in Microsoft Excel to summarise, group, and analyse data meaningfully for reports, audits, or team reviews.

Begin using subtotals and make those spreadsheets intelligent, organized, and user-friendly!


🎓 Learn More with Other Levels

Take your Excel skills to the next level:


✍️ Other Articles, Tips & Tricks you would like:


Work Faster, Smarter, Productively and Efficiently with Other Levels Dashboard Template

Latest Stories

View all

Manage Large Datasets with Microsoft Excel

Learn how Structured References in Excel Tables can streamline your formulas, enhance data readability, and boost productivity in managing large datasets like sales reports, inventories, and dashboards.

Read more

Audit Formulas with Microsoft Excel's Trace Precedents and Dependents

Unlock the full power of Microsoft Excel by mastering Trace Precedents and Trace Dependents! The Ultimate tool for formula auditing, data flow tracking, error detection, and building flawless, professional-grade spreadsheets. Perfect for accountants, data analysts, financial modellers, and anyone working with complex Excel sheets.

Read more

Create Sales Commission Calculator in Microsoft Excel

Easily calculate, manage, and automate sales commissions with Microsoft Excel—ideal for sales teams, freelancers, and businesses. Customize tiered rates, track totals, and simplify payroll with powerful formulas and dynamic dashboards.

Read more