Learn how to use Excel's Subtotal feature to quickly add summarizing rows to your data.
Almost every month, I suggest the Subtotal feature to a reader. It's an easy-to-use feature that summarizes data by groups. You define the group and the summarizing function; Excel does the rest. This month, I'm going to show you how to use Subtotal and share a few tips that will help you get the most out of this feature.
We'll be working with a simple dataset, and you can download the example .xlsx or .xls file. I used Excel 2013 on Windows 7, but the instructions for all three ribbon versions are virtually the same. In Excel 2003, you'll find Subtotals on the Data menu. While the example file is simple, you'll quickly see how convenient this tool can be with lots of data!
Before you begin
If you compile data in any context, you quickly learn that consistency is the key to success. All the powerful features in the world won't help you if your data is inconsistent. Excel's Subtotal feature is no exception to this rule. Each group's value must be the same for all the records in that group. For instance, if users enter Research and Development, R&D, and Reach & Development, Excel will return a summary row for each of those values. Excel won't interpret the different values as the same, even though they are. Excel offers a number of features for helping in this area; you can use data validation, data types, and even data entry forms.
The Subtotal feature requires that your values be grouped. This means that you must sort your data by the group values before you implement the Subtotal feature.
A simple Subtotal
Now, let's use the Subtotal feature to return the total number of orders for each company shown in Figure A. To do so, complete the following steps:
- We're grouping by company names, so the first step is to sort by that column. Click inside the Company column and choose Sort A To Z from the Sort & Filter drop-down (Figure A) in the Editing group (Figure B).
- With the cursor still in the Company column, click the Data tab.
- Click Subtotal in the Outline group.
- In the resulting submenu, select Company from the At each change in drop-down (should be the default).
- Select Sum from the Use function drop-down.
- Check Orders in the Add subtotal to list (Figure C).
- Click OK.
The results are shown in Figure D. Below each company this feature inserts a summarizing row. Company A's total is 39000, Company B's total is 30000, and so on. That was certainly easy!
Subtotal inserts a summarizing row.
While the subtotal feature is active, Excel displays a pane to the left that shows three viewing levels:
- 1 displays one the grand total row
- 2 displays the subtotal rows
- 3 displays everything
With a quick click, you can display everything, only subtotals, or only the grand total. To temporarily hide (and unhide) this pane, press [Ctrl]+. You can hide the pane without removing the subtotaling rows.
Add another subtotal
In the first example, we totaled one column, the Orders column, but you're not limited to only one column. Let's add a totals row for the Returns column as follows:
- With the cursor still in the Company column (this isn't critical, you can choose any column you like in the resulting dialog), click Subtotal.
- Check Returns without unchecking Orders (Figure E).
- Click OK. The results are shown in Figure F.
In the first example, the cell to the right of each Orders subtotal was blank. Now, it contains the subtotal for the Returns values. You can total every numeric field in your data set. What you can't do is change the function for different columns within the same row.
Add another row
You might think one subtotaling function is all you get, but fortunately, that's not the case. Let's add an average row to the data set:
- Click Subtotal.
- In the resulting dialog, choose Average from the Use function drop-down.
- Uncheck the Replace current subtotals option (Figure G).
- Click OK to see the results shown in Figure H.
Now, there's an average and total for each company's orders and returns. If you don't uncheck the Replace current subtotals option (#3), the feature replaces the existing row instead of adding a second row.
There's nothing wrong with the default formatting, but you can change it to help distinguish the subtotal rows from the data. Fortunately, you don't have to format each row individually. There's a quicker way:
- Click 3 in the Subtotal pane (to the left) to hide the detail records (Figure I).
- Press [Alt]+; to select only the visible cells.
- Apply the format. I chose a (light) blue font. (I don't recommend such a light blue; it simply offers a stark contrast to the black.)
- Press 4 in the side pane to review all of the records. As you can see in Figure J, only the subtotaling rows are blue.
You formatted all of the visible cells, which means existing data and new data in all visible cells will be blue. If your sheet contains anything other than your dataset, this shortcut won't work for you most likely.
Eventually, you might want to remove the subtotaling rows. To do so, click Subtotal in the Outline group and then click the Remove All button at the bottom of the resulting dialog box. Unfortunately, you can't use the Subtotal feature with a table. You can convert the table to an ordinary range by right-clicking the table, choosing Table from the resulting shortcut menu, selecting Convert to Range, and then clicking Yes to confirm this action. At this point, you can apply the Subtotal feature as you normally would. Then, when you're done, convert the range to a table. If you don't like the default labels added by the Subtotal feature, use the Find & Replace feature to change them.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at firstname.lastname@example.org.