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:
-
🌐 Visit our Other Levels Website
-
📺 Watch free tutorials on our Other Levels YouTube Channel, and learn Microsoft Excel from beginner to advanced.
✍️ Other Articles, Tips & Tricks you would like:
- Easy your Formula Management in Microsoft Excel
- Create a Progress Bar in Excel Using REPT Formula-No Charts Needed!
- Build an Attendance Tracking HR Dashboard in Excel-Step-by-Step Guide
Share:
Format Numbers and Dates Your Way with Microsoft Excel
Quickly Turn Text to Column in Microsoft Excel