## 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

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)

=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:

Bonus Tip

Get started >>

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