Save time with subtotal formulas
Subtotals save time, reduce errors and provide confidence. Learn how to use subtotal formulas to speed up your spreadsheets and cut down editing time.
Know your way around subtotals already? Check out this tip to see if you’re getting the most out of them.
How to use subtotal formulas in Excel
Learn when and how to use subtotals in Excel, and get keyboard shortcuts to insert subtotals quickly.
You can use subtotals in many ways, giving you quick calculations and confidence in formulas.
Here’s how to use the full power of subtotals.
How do subtotals work?
If you are already familiar with subtotals, you can skip this section and go straight to the subtotals shortcut.
When you have a worksheet with data in several groups that you want to subtotal, you could use the SUM function to build each total and then use another SUM function to create a grand total from all the subtotals.
If you use the SUBTOTAL function instead, there are some obvious differences.
When using a SUBTOTAL formula:
- Subtotals and grand totals are each calculated from underlying data
- Grand totals ignore any subtotals within the selected data
Advantages of using SUBTOTAL instead of SUM
These differences give you some advantages, including:
- Subtotals cannot accidentally be included more than once
- Subtotals cannot be unintentionally omitted
- Accuracy of grand totals does not depend on subtotals being correct
- When new categories and subtotals are inserted or deleted, the grand total formula does not need to be updated
Function syntax for SUBTOTAL formulas
The SUBTOTAL function is similar to the SUM function, but requires inserting 9, after the opening parenthesis, like this:
Formula using SUBTOTAL function
=SUBTOTAL(9,A1:A10)
Formula using SUM function
=SUM(A1:A10)
For example
In this example, we calculate and display subtotals for 3 different categories, each containing data from 3 different locations.
These are shown using SUM formulas, and also using SUBTOTAL formulas. The end result looks the same.
You can use this example to compare the differences between SUBTOTAL and SUM.
Example output
The end result will look like this:
Example using SUM
If using SUM to build these formulas, they would look like this:
Example using SUBTOTAL instead of SUM
Using SUBTOTAL, the formulas would look like this:
Please log in to see the bonus tip.
Don’t have a login?
Get a login for free on our standard plan, and get a new tip each week.
All logins also get access to the bonus tip each week.
Get even more
Get even more with one of our reasonably priced paid plans, with access to features like:
- Quicksheet PDF summaries
- Example files
- VBA syntax
- Additional expert tips each week
- Advance publication
View our plans to see what else you are missing, or join for free on our Standard plan.
Please log in to see the expert tip.