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.

Quick Tip

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:

Subtotal example end result
Subtotal example end result
Example using SUM

If using SUM to build these formulas, they would look like this:

Subtotal example using SUM formula
Subtotal example using SUM formula
Example using SUBTOTAL instead of SUM

Using SUBTOTAL, the formulas would look like this:

Subtotal example using SUBTOTAL formula
Subtotal example using SUBTOTAL formula

 

Bonus Tip

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.

Get started >>

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

Get quicker >>

View our plans to see what else you are missing, or join for free on our Standard plan.

 

Expert Tip

Please log in to see the expert tip.