Tutorial For How To Do Subtotals in Microsoft Excel
This article will not only teach you how to do subtotals in Microsoft Excel, but I also provide a few handy tricks along the way.
What is a Subtotal?
Microsoft defines it as quickly calculate rows of related data by inserting subtotals and totals. In other words, it’s an automatic way of adding totals based on a grouping of data. This is a feature built-into Excel.
What the Sample Data Looks Like
We’ll be creating a subtotal that is a count of the number of people in each US State, for a particular dataset. This fictitious data was downloaded from the website Mockaroo. The full data set has 1,000 rows; only the first few are shown in the screenshot below so you can see the column names:
How to add Subtotals to a data table in Excel
1. (Optional) Ensure the first row are column headers
I’m often able to do this with the data I work with, but it may not be possible in all cases. Sometimes there are some extra rows at the top, and my data really doesn’t start until, say, row 5. I tend to delete the blank/unused rows at the top; such that the column headers are in the first row, and the actual data begins in row 2. I just find it easier to proceed this way.
I’m going to assume and make reference in the remainder of this tutorial that appropriate column headings were defined.
2. Prepare the data (only if needed)
Usually I need to sum data. Sometimes I need to count items. Often when I need to count something, I’ll add an additional column with a value of 1, and then I’ll sum this value.
In this tutorial, I’ll add a column called “count” (Column F below) and populate the value in every cell in that column with the number “1”.
Check out my previous tutorial on how to copy a value/formula across many cells quickly!
Now the data looks like this:
3. Sort the data
You need to sort the data by what you will be grouping it by. Excel creates the subtotals based on if the next row is the same as the previous row. If you skip this step, then the final result may have duplicate entries.
For our example, we want a count of people by state. So we need to sort the data by state.
a) Highlight the data you want to sort. I tend to highlight only the actual data (vs the entire worksheet; or even the entire column, which would then select additional blank rows below the actual data).
b) From the “Data” ribbon, select “Sort” from the “Sort & Filter” section.
c) In the Sort window, check off the box for “My data has headers” if applicable. Then in the “Sort by” dropdown, select the field to sort by (i.e. have the subtotal by). For this example, select “state”. Then click “OK” to close this window and perform the sort on your data.
Your data should now be sorted by the column that you plan to have subtotals on:
4. Perform the Subtotal
a) First: make sure you highlight just the data, with the column headers (don’t include extra columns or extra blank rows at the bottom). Then from the Data ribbon, in the “Outline” section, select the option for “Subtotal”
If you’re on a smaller screen (ex. a laptop) then the “Subtotal” option may not be visible. Instead, you’ll have to click on the “Outline” section first. Then click the “Subtotal” option.
b) Now you’ll see the Subtotal window:
Notice it says at each change in [something] use the function [some math function] add subtotal to [something].
Assuming your first row of data are the column headers, that will be displayed in the dropdown and also in the list with checkboxes. Otherwise it’ll say (Column A) / (Column B) / etc.
By default the first item in the dropdown will be selected, which is the first (left-most) column of your dataset. Change the value to what you want to subtotal on (here, “state”).
The math function we’re going to use for this example can either be “Count” or “Sum”. I’ll use “Sum,” and we’ll sum all of the 1’s that are in the column called “count”.
Pro Tip: If you need to select a “lot” of checkboxes, you can use the keyboard shortcut of “space bar” to toggle each checkbox in conjunction with the arrow keys. I’ll often go to the bottom of the list of checkboxes, select the last item, and then quickly alternate between pressing the “up” arrow key and then “space” to quickly select multiple checkboxes one after the other.
After you’ve selected the necessary columns to apply the function to, hit the “OK” button. Here, we’ve selected the one column called “count” and we’re going to Sum the values.
When doing Subtotals, you’ll generally want to perform the function on numerical data.
The Excel worksheet now looks like the screenshot below. You can see it automatically inserted rows 5, 13, and 17 (and then more, not visible).
5. View the Subtotals
You’ll notice on the far-left side next to the data there are now smaller numbers; 1 to 3. You can click on these numbers to toggle the detail visible.
Clicking on the “2” will collapse (hide) the details and show what you subtotaled on:
Clicking the “3” will expand everything, showing all of the details (the last image in Step 4 above).
6. (Optional) Make the Subtotals Easier to Read
Let’s highlight only the rows that have subtotals on them; it’ll make it easier to read when the data is expanded (i.e. in View “3”).
a) Collapse the data (i.e. click on View “2” as seen in Step 5 above)
b) Highlight the data
c) Go to the “Go To Special” menu: in the “Home” tab >> “Editing” section >> “Find and Select” >> “Go To Special”
Keyboard shortcut on Windows: Ctrl + G, then hit “Special”
d) Select “Visible cells only”.
e) Click the “OK” button
As the name implies, only the visible cells are highlighted. In the screenshot below, the following rows are not visible and thus not selected: 2, 3, 4, 6, 7, 8, 9, 10, 11, 12, 14, and more:
Now you have the ability to format only the cells that are visible. For example, you may want to change the background color of the cell:
Make the color selection, and then click off the data set. When you expand the data set (clicking on the Subtotal View “3” to the left of the numbers) you’ll see it’s much easier to read where the subtotals are!
Some Last Notes
Subtotals Within subtotals
It’s possible to have subtotals within subtotals. Perform the same steps as before, the only difference is that in the subtotal window, uncheck the box for “Replace current subtotals”
By not replacing the existing subtotals, you’re adding additional subtotals on.
Important: be sure to sort the data ahead of time first by the original (outer-most) value, then by the newer value. This was done in Step 3 above. If you don’t do this, then the values may show duplicative data. In the example below, I sorted on State then also by City before doing the subtotals
This will add a 4th view at the far left, allowing you to toggle the amount of detail shown:
And of course you can do the fancy highlighting trick; I suggest picking two different colors to make it easier to read:
Something to watch out for: I’m not sure how this happens, but sometimes after doing subtotals over-and-over on the same data, Excel gets confused. Even while writing this tutorial, I somehow had an extra layer of detail that wasn’t needed (instead of 4 views above, as it should be, I somehow ended up with 5 views).
Which leads me to the last tip:
How to Remove Subtotals
From the Subtotal menu, just click the box for “Remove All”:
While the subtotals are removed, I’ve noticed sometimes Excel does weird things when you put subtotals back on. When that happens I re-copy the data (without the subtotals) to a new worksheet, and proceed as before.
