• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

Excelypedia

The Excel Encyclopedia of The Internet

  • Tutorials
  • Functions
  • Formulas
  • Templates

Subtotals in Excel

August 15, 2021 by Andrew Childress
Subtotal at each change

While it's helpful to have your data in a spreadsheet, it's only the beginning of analyzing it. Let's learn how to use Subtotals in Excel.

While it’s helpful to have your data in a spreadsheet, it’s only the beginning of analyzing it. You need to use the analysis tools to subtotal and calculate so that you understand what’s inside your spreadsheet.

Instead of manually totaling up groups of data, let’s learn how to use the built-in feature for subtotals in Excel.

Step One: Sort Data in Excel

Before we add subtotals, it helps to organize and sort our data. Excel’s subtotal feature will sum up data each time it sees a change in the list, so we first need to group our data.

With your columns highlighted, go to the Data > Sort option on Excel’s ribbon. You’ll see a new menu pop up that helps you decide how to sort data. For my example data, I’m going to sort all of the brands together, then sort based on year. Basically, it will alphabetically sequence the brands, then sort the years. See the Sort menu screenshot below.

Data sort subtotal in Excel

Make sure you have one rule for each column (except the numeric column.) In my example that means that there are two sort rules. Press OK, and your data is now sequenced.

Step Two: Add Subtotals in Excel

Once you’ve sorted your data, make sure your columns are highlighted  and choose Outline > Subtotal. 

Data outline subtotal

Now, it’s time to tell Excel how to add your subtotals. There’s an option labeled At each change in. Basically, this tells Excel where to add subtotals. In my case, I’ll choose Brand so that each brand is subtotaled.

Make sure you leave Use function set to Sum, then tick the box for the column with your values. In my case, that’s Sales. Press OK when complete.

Subtotal at each change

Now, your data is subtotaled by brand. You have an easy way to see the totals for each brand.

Subtotal complete in Excel

Don’t manually total your data by category, Instead, add subtotals and decide on your groups. Then, Excel handles the hard work for you.

Category: Excel FunctionsTag: Excel, How To, Instructions, Tutorial

About Andrew Childress

Previous Post:Currencies in ExcelCurrencies in Excel
Next Post:Gridlines in Excel

Sidebar

Recent Posts

  • How to Do a Contingency Table in Excel
  • Format Cell Alignment in Excel
  • Default Width in Excel
  • Translate in Excel
  • Switch Windows in Excel

Categories

  • Basics
  • Examples
  • Excel Formulas
  • Excel Functions
  • Excel Shortcuts
  • Excel Templates
  • Excel Tutorials
  • Uncategorized

Excelypedia

Master Excel to add a highly valuable asset to your employability portfolio. Learn how to analyze data in Excel.

    • Facebook
  • Twitter
  • Instagram
  • YouTube

Learn

  • Functions
  • Formulas
  • Shortcuts

Legal

  • Privacy Policy
  • Cookie Policy
  • Terms & Conditions

Help

  • FAQ
  • Contact Us

Copyright © 2023 · Excelypedia · All Rights Reserved

Back to top