Running multiple calculations on your Excel tables can sometimes feel a little bit messy. You want to maintain a nice reporting format for your information, but you don’t need to keep your formulas separate from your tables, so that information doesn’t get double counted. You end up looking for places to put in your calculations without their getting in the way.
This comes up often when you want to prepare multiple subtotals for your data. And what you may not have realized is that Microsoft has included a function that will allow you to easily include those calculations directly in your table without causing any inaccuracies.
It’s called the SUBTOTAL function, and this guide will give you an overview on how to work with it. If you would like to expand your knowledge on the function, as well as other tools that will simplify your work in Excel, you might also be interested to take a course on formulas and functions in Excel.
Understanding the usefulness of the SUBTOTAL function
Using the subtotal function, you can perform a number of calculations and keep them in line with the rest of your data. How this differs from other functions is that the results of those calculations will be excluded from a range you select to perform a function on.
This is best understood through example. So take a look at the sample table to the right.
In this table of orders, you want to include sub-totals for each month, and a three-month grand total at the end. Now, you may be thinking that the SUM function is what you would need for this. However, operating this way, you will need to be careful in your set up so you don’t include any double counting.
Using SUM, you would use three functions. The first would go in cell C11 to calculate the SUM of C3 through C10: =SUM(C3:C10)
Your second and third functions will go in cells C20 and C28, respectively, to sum C12:C19 and C21:C27.
Then, for cell C29, your SUM function would need to add together the ranges for the three months. Summing the range C3:C27 would be incorrect because it would add the intermittent sub-totals. Instead, you’d need to structure the function like this: =SUM(C3:C10, C12:C19, C21:27)
Certainly, this will produce an accurate result, but it is a bit clumsy to work with, particularly if this table will be used on an ongoing basis, for new data. That is where the SUBTOTAL function comes in to simplify your formulas and add flexibility. Completing this table with Subtotal functions, you can calculate your total using the complete range, as the monthly totals will be excluded from the larger sum calculation.
Creating Subtotal functions
Now that you have an idea of the advantage you get from the SUBTOTAL function, let’s work through this sample table and apply it.
Your first sub-total goes in cell C11 for November orders.
Similar to the SUM function, your subtotal funtion will work by referencing the range that you want to sum. However, you need to additionally include a specification to tell the function the type of calculation you want to perform. This will be indicated with a number, preceding the range input.
In short, the subtotal calculation allows you to choose from different types of calculations, each corresponding to a number. We will review the available options in the next section. For now, you will want to know that number for a SUM calculation is 9. So the formula you will want to use is as follows:
You can plug this directly in to the formula bar, or you can opt to use the function’s dialog box. For the latter option, you will want to follw these steps:
1. Highlight cell C11. Then click the Insert function button at the left of the formula bar
2. From the category selection box, choose Math & Trig.
3. In the Select a function menu, scroll down and select SUBTOTAL
4. This will bring up the dialog box for the SUBTOTAL function. Here you have input boxes to specify the arguments needed to perform the function. Type in 9 for the function number and C3:C10 for the reference input.
5. Click OK. If you entered the same values as the example, you should see the result $472.00.
Now you will want to complete the SUBTOTAL function for the additional sums, including the complete sum for the three month period. For cell C20, you will want to sum the range C12:C19, and for cell C28, you want to sum C21:C27.
In cell C29, you can use the subtotal function on the complete range, C3:C28. You will see that this automatically excludes the subtotal values to eliminate double counting.
References in the Subtotal function
As you will have gathered at this point, you are not limited to SUM within the SUBTOTAL function. Instead, the function offers 11 different calculations you can use, each referenced by a number. Here is a reference for those 11.
Returns the statistical mean for the specified range
Counts the cells within the range that contain numeric values
Counts the cells within the range that are not blank
Returns the highest numeric value within the range
Returns the lowest numeric value within the range
Multiplies together the numbers within the range
Returns the standard deviation based on a sample of the population
Returns the standard deviation for an entire population
Returns the sum of the numbers in the range
Returns the variance for a sample population
Returns the variance for an entire population
So, in short, you can use the SUBTOTAL to simplify completing subtotals for several calculations you need to perform on your data. Depending on your use of the program, some of these might prove more useful than others. For instance, unless you are engaged in population statistics, you may not need the STDEV operation. However, functions like AVERAGE can significantly improve your efficiency in working with Excel.
If you feel, at this point, as though you need to get a better grasp on these functions and what they do, you can get the information you need in a comprehensive Excel course.
Let’s add a COUNT function in the sample spreadsheet. This time we’ll type it directly in to the formula to find a helpful guide that Microsoft has programmed in to make things easier.
1. Insert a new line above line 12. Simply right-click on the row label at the left of row 12 and select Insert from the drop-down menu.
2. You now have a new row in which you can place the new SUBTOTAL function. We’ll want to put the function in cell C12. So double click in that cell to begin entering a formula.
3. Invoke the subtotal function by typing =SUBTOTAL(. You will see that a helpful reference pops up to help you select options for your formula
4. Even if you didn’t have your function number handy, you can see that 2 corresponds to the COUNT function. So type in 2 to indicate a COUNT function. Then type a comma. Your cell should now look like this:
5. Select the range for the function. You can simply click and drag to do this, as Excel will hold your place in the formula.
6. Finish the formula with a closed paranthesis and press enter/return.
Notice that the result will come back in currency format. You will want to change that to be a simple integer instead.
Another advantage you can gain from using the Subtotal function is excluding hidden cells from your subtotals. If you hide cells in a range on which you perform a SUM function, the hidden cells will still be included in the calculation. However, with the SUBTOTAL function, you can specify whether those numbers should be included in your sum.
Indicating whether the function should include or ignore hidden cells is done with the reference number. If you want your function to include hidden cells in your subtotals, you will reference them with the standard 1-11 reference numbers. However, if you’d like the function to exclude the hidden cells when calculating your subtotals, you will want to add 100 to the reference number. So the reference numbers for Subtotal functions that exclude hidden cells become 101-111.
You can easily change the examples we just created to exclude hidden cells from the subtotal. Let’s try it out on the first sum in the sample spreadsheet.
1. Select cell C11, which contains the first subtotal sum. In the formula bar, you will see the SUBTOTAL function written as follows:
2. Change the reference number 9 to 109
3. Hide cell C10 to see how the sum changes. to do this simply right-click on the row label for row 10 and select Hide from the drop-down menu.
You should see the sum change from $472.00 to $383.000.
Creating multiple subtotals for an entire table at once
Now that you have learned the ins and outs of the subtotal function, you can get to know a tool that will make the process even simpler in your regular practice with Excel. This is, without a doubt, the quickest way to add subtotals to your list. However, it will require a slight modification to the example this guide has used thus far.
To prepare the spreadsheet, you will want to delete all of the rows containing placeholders for the subtotals. You will want the input values to be continuous. You’ll also want to delete the placeholder for the grand total at the bottom of the table.
Next, since this table spans over three periods (one month each), you want to add a column indicating which period each entry falls in to:
With the table prepared as such, you are ready to use Excel to take the work out of calculating subtotals for you. Here is what you’ll do.
1. Select the complete table, from A3:C25
2. Click on the data tab to bring up data options on the main ribbon.
3. From the Outline group, select Subtotal
4. The Subtotal dialog box will come up, allowing you to choose your options for the function.
The first option in this dialog box essentially tells Excel how to determine when to enter a subtotal. This is the reason for entering the additional column containing period numbers. So, by designating period in the first drop-down, you are saying that you want a subtotal at the point where each period changes.
Next, in the Use Function drop-down, you can choose from the 11 options described earlier in this guide. We are using the SUM function in this example.
The selection box labeled “Add subtotal to:” determines which column contains the information you want to run the calculation on. In this case, of course, it is cost. So that should be the option you select.
You will see that there are additional settings, indicated by the checkboxes. You can replace any current subtotals you have in the table, add a page break between each of your groups, and add a summary at the end. Since we want to have a grand total at the bottom of our table, we’ll want to make sure this third option is checked.
5. Once you have these parameters set, click OK.
Voila! You have a subtotaled list!
This handy tool is only one of many that Excel offers to perform quick calculations on a table of data. Try an intermediate Excel course if you’d like to learn about more of these great time-saving tools.
If you have followed along with this brief guide, you hopefully have a better grasp on how to include subtotals, right in line with your data, without putting your formulas and calculations all over the spreadsheet. This should be a helpful step in preparing outstanding reports in Excel that will wow your colleagues and management. If you would like to learn more, you can get some great tips in a course on how to build excellent dashboards and reports in the Excel program. Best of luck!