Copyright

How to Insert a Subtotal in an Excel Worksheet

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

To save time, inserting a subtotal in an Excel worksheet can be done easily through organizing your data and using Excel's automated tasks. Explore more about inserting a subtotal, including using the Subtotal feature, sorting the data, and navigating the outline. Updated: 10/28/2021

The Subtotal Feature

Excel is full of time-savers. There is nothing better than using a tool that helps to automate tasks, and the Subtotal feature is one of them. Subtotals are the calculations or formulas added to a cell that sum or total a part of a group in a column of numbers. The feature uses common functions, like SUM, COUNT, PRODUCT and AVERAGE, and summarizes your data by creating groups.

In this lesson, we will look at a couple of examples using the Subtotal command and learn how to insert subtotal rows into your worksheet data.

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: How to Create a Table in an Excel Worksheet

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 The Subtotal Feature
  • 0:34 Sort Your Data First
  • 2:22 How to Insert Subtotals
  • 3:45 Summary, Outline and Totals
  • 4:56 Beyond Sum
  • 6:18 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Sort Your Data First

There are a couple steps you need to take in preparation for using the Subtotal command. In order for everything to work out correctly, you first need to sort your data. So let's use an example and quickly review how to perform a sort.

Imagine we have a list of sellers and sales results. There are seven columns: Region, Sales Rep, Q1, Q2, Q3, Q4 and Year End. Currently, the worksheet is sorted by Sales Rep name, but ultimately, we want it grouped and subtotaled by Region.

And let me give you a tip. The feature works best when the data you want to group by is in the first column. For instance, in our example, we want it grouped by region, which means we should have our first column display the region where the sales rep is located. Our example has four regions: West, East, North and South. Let's go ahead and sort our data by region.

  1. Select or highlight the data to sort (you can click on a cell with data and press Control + A on your keyboard)
  2. Go to the Data menu in the ribbon
  3. Look in the Sort & Filter grouping of commands
  4. Click on Sort
  5. In the dialogue box, make sure My data has headers is selected
  6. Select Region in the Sort By option box
  7. Click OK

Perfect! Now our information is sorted by region, and we are all set to add our subtotals.

How to Insert Subtotals

The subtotal feature really performs two functions. First, it will group and summarize the data. In our example, the grouping will be by Region. Next, it will insert a row for the subtotals and add the formulas to total the revenue numbers. Here are the steps.

  1. Select or highlight the worksheet data
  2. Go to the Data menu in the ribbon
  3. Look in the Outline grouping of commands
  4. Click on the Subtotal command and you'll notice a Subtotal dialogue box will open
  5. In the Add subtotal to box, select Q1, Q2, Q3, Q4 and Year End
  6. Confirm the box for Replace current subtotals is selected
  7. Confirm the box for Summary below data is selected
  8. Click OK

Sweet! Nothing like automated tasks. We didn't need to stress over inserting rows and how to write the formulas. Excel did all the work for us. Notice that Excel did more than just add subtotals, so let's take a detailed look at our summarized information.

Summary, Outline and Totals

The outline is on the far left of the worksheet
sample spreadsheet

The first thing you will notice is an outline. Look on the left side of the worksheet and you will see boxes with numbers and plus (+) and minus (-) symbols. The boxes with numbers are the levels, or the hierarchy of the groups, and there are three levels. You can click on each box and expand or collapse the levels. The plus or minus symbols allow you to expand or collapse the details of the information. Minus (-) will hide details and the plus (+) will show details.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it now
Create an account to start this course today
Used by over 30 million students worldwide
Create an account