Quick Links
Summary
To use the SUBTOTAL function, create a formula using the syntax: SUBTOTAL(function_number, reference 1, reference 2,…). You can also use the Subtotal feature by selecting your rows, then clicking the “Data” tab. In the Outline drop-down menu, select “Subtotal.”
Obtaining subtotals for groups of related items is easy in Microsoft Excel. In fact, you have two ways to do this. You can either use the SUBTOTAL function or the Subtotal feature, whichever works best. We’ll show you how.
What Is a Subtotal in Excel?
As mentioned, SUBTOTAL in Excel allows you to group the same or related items in a list and use a function to calculate the values. For example, you can use it to sum sales by month or average grades by student. You could also add inventory by product orcount the numberof bills due next week.
Using the SUBTOTAL function by creating a formula or using the Subtotal feature, you’re able to get the calculations you need in just a few steps.
Use the Excel SUBTOTAL Function
you’re able to create a formula using the Excel SUBTOTAL function with the flexibility to include or exclude rows you’ve hidden.
The syntax for the formula is
where the first two arguments are required. You can use additional cell references ornamed rangesfor the remaining arguments as needed.
The
argument allows you to insert one of 11 functions using its corresponding number. The first 11 include hidden rows, while the second 11 exclude them. So, simply insert the number forthe function you needand how you want to handle the hidden rows.
1
101
2
102
3
103
4
104
5
105
6
106
7
107
8
108
9
109
10
110
11
111
As an example of the SUBTOTAL function, we’ll sum the total of sales in the cell range B2 through B4 using this formula:
For this next example, we hid rows 4 and 5. Using this first formula, we can obtain our sum using the number 9 for the first argument to include the hidden data.
Now, we’ll exclude those hidden rows from our total, using the number 109 for our first argument.
Once you have your subtotals, you can use the SUBTOTAL function once more for a grand total at the bottom. Alternatively, you can usethe SUM functionto add the subtotals.
Related:How to Sum a Column in Microsoft Excel
For another example, we’ll use theaverage functionto include hidden rows 3 and 4 with this formula:
Notes on the SUBTOTAL Function
Here are a few things to keep in mind when using the SUBTOTAL function in Excel:
Use the Subtotal Feature
Another way to use the SUBTOTAL function in Excel is by using the Subtotal feature. This way, you can automaticallyadd calculationsand group the items at the same time. Excel uses the SUBTOTAL function to accomplish this for you. Let’s look at a couple of examples.
Related:How to Calculate Workdays With a Function in Microsoft Excel
Using the Subtotal feature for our sales by month, we can group the data per month and sum each group with a grand total at the bottom.
Select all of the rows you want to group and subtotal. Go to the Data tab and pick “Subtotal” in the Outline drop-down menu.
When the Subtotal box opens, choose how you want to add the subtotals.
Optionally check the boxes at the bottom for the additional items as you like. Click “OK.”
You’ll then see your data update to groupandsubtotal the rows and create a grand total at the bottom. Use the plus, minus, and number buttons to collapse or expand the groups for easier viewing.
As another example, we’ll use student grades. We’ll use the Subtotal and Group features to display an average grade for each student. Here’s the process.
Select the rows, go to Data, and pick “Subtotal” in the Outline drop-down menu.
In the Subtotal box, we’ll choose Student in the change drop-down list and Average in the function list. We’ll then check the box for Grade. Click “OK.”
We now have our rows grouped by student with an average grade for each and an overall average at the bottom. Again, you’re able to use the buttons on the left to collapse and expand the groups.
If you decide to ungroup the rows after you use the Subtotal feature, the rows return to normal. However, those containing the SUBTOTAL function used by Excel remain for you to continue using or simply delete if you prefer.
The Subtotal feature can become complex if you plan to use many groups. However, these basic examples should help get you started if you’re interested in this function.
One thing to note is that you can’tadd an Excel tablesubtotal with this feature. If you have your data in a table, you can either insert the Excel formula for SUBTOTAL as described earlier orconvert your table to a cell rangeto use the feature. If you choose the latter, you’ll lose the table functionality.
Now that you know how to insert subtotals in Excel, check outhow to remove duplicate rows.
Basic·Budgeting·Data Entry·Logical·Text·Time and Date
Explained
Copying Formulas·Evaluating Formulas·Finding Functions·Fixing Formula Errors·Functions vs Formulas·Comparing Lookup Functions·Locking Formulas·Structuring Formulas·Translating Formulas
