Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.
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.
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.
- Select or highlight the data to sort (you can click on a cell with data and press Control + A on your keyboard)
- Go to the Data menu in the ribbon
- Look in the Sort & Filter grouping of commands
- Click on Sort
- In the dialogue box, make sure My data has headers is selected
- Select Region in the Sort By option box
- 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.
- Select or highlight the worksheet data
- Go to the Data menu in the ribbon
- Look in the Outline grouping of commands
- Click on the Subtotal command and you'll notice a Subtotal dialogue box will open
- In the Add subtotal to box, select Q1, Q2, Q3, Q4 and Year End
- Confirm the box for Replace current subtotals is selected
- Confirm the box for Summary below data is selected
- 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 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.
The next thing you will notice is that Excel summarized the information by creating groups and, of course, inserting a row for the subtotals. Then Excel added the formulas and totals for the quarters and Year End. In addition, a Grand Total was added to the bottom of our columns.
Note: the Subtotal command recalculates the subtotal and grand total values automatically as you edit the detail data.
One last thing before we wrap up the lesson. In our example, we used the SUM function. In other words, we wanted to add a formula that added numbers together. However, you can also use other common functions, such as COUNT and AVERAGE. Let's go back to our example.
With the subtotals added, we can add other totals. Imagine the list is super long, with hundreds of sellers in each region. We want to also see how many sellers are in each region. This would help to make a fair comparison between the regions and their totals, since a region with more sellers should have more revenue and vice versa. Let's get another total added, but this time, the number of sellers in each region.
- Select or highlight the data
- Click on the Subtotal command
- Select COUNT in the Use function box
- In Add Subtotal to, uncheck all the boxes and add a check to the Sales Rep
- Uncheck Replace Current Subtotals since we still want our sales totals
- Click OK
Notice that another row was inserted, displaying the number of reps for each region.
This lesson introduced you to the Subtotal feature in Excel. This is one of the many time-saver commands that will help you to automate common worksheet tasks. You learned that sorting the data first is key, and that it's helpful to have the first column display the information you want Excel to group.
The Subtotal command is found under the Data menu in the ribbon, in the Outline grouping of commands. Once your data is sorted, simply click on the command, select how you want to summarize and total the information and click OK.
Next time you need to add totals and subtotals, try the Subtotal command in Excel.
Once you have finished this lesson, you should be able to calculate totals on your spreadsheet by using Excel's subtotal command.
To unlock this lesson you must be a Study.com Member.
Create your account
Register to view this lesson
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
Already a member? Log InBack