The SUBTOTAL function is a powerful tool that can be used to add up the totals of different data items. This function can be used to calculate the total cost of a purchase, the total amount of money that has been spent on a purchase, or any other calculation that needs to be made with data. To use the SUBTOTAL function in Microsoft Excel, first open the Microsoft Excel spreadsheet you want to work with. Then, use one of the following methods to enter the data you need to calculate the SUBTOTAL:
- Type in all of the data you need to calculate the SUBTOTAL into one field on your spreadsheet. Then, press ENTER.
- Type in a number into one of the other fields on your spreadsheet and then press ENTER. The SUBTOTAL function will automatically add up these numbers and display them in one field on your spreadsheet.
- If you want to save your calculations for later use, you can press CTRL+S (Windows) or CMD+S (Mac) and then click on the “save as” button located near the top of your spreadsheet.
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 or count the number of bills due next week.
RELATED: How to Use the COUNT Function in Microsoft Excel
Using the SUBTOTAL function by creating a formula or using the Subtotal feature, you can get the calculations you need in just a few steps.
Use the Excel SUBTOTAL Function
You can 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 SUBTOTAL(function_number, reference 1, reference 2,…) where the first two arguments are required. You can use additional cell references or named ranges for the remaining arguments as needed.
The function_number 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 for the function you need and how you want to handle the hidden rows.
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 use the SUM function to add the subtotals.
RELATED: How to Sum a Column in Microsoft Excel
For another example, we’ll use the average function to include hidden rows 3 and 4 with this formula:
And next, we’ll exclude the hidden rows with this formula:
Notes on the SUBTOTAL Function
Here are a few things to keep in mind when using the SUBTOTAL function in Excel:
The function works for columns or vertical ranges, not rows or horizontal ranges. If you have nested subtotals in the reference argument area, Excel ignores these in the subtotal result. When you use a filter, the data filtered out is excluded from the subtotal result.
Use the Subtotal Feature
Another way to use the SUBTOTAL function in Excel is by using the Subtotal feature. This way, you can automatically add calculations and 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.
At Each Change In: Select the column you want to use for the grouping. Here, we picked Month so that a new group is created when the Month changes in the sheet. Use Function: Select the function you want to use, such as sum, average, minimum, maximum, or another option. For our example, we selected Sum. Add Subtotal to: Check the box for where you want the subtotal to calculate. For our example, we picked Sales.
Optionally check the boxes at the bottom for the additional items as you like. Click “OK.”
You’ll then see your data update to group and subtotal 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 can 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’t add an Excel table subtotal with this feature. If you have your data in a table, you can either insert the Excel formula for SUBTOTAL as described earlier or convert your table to a cell range to 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 out how to remove duplicate rows.
