Tutorial For How To Do Subtotals in Microsoft Excel

Image for post
Image for post

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?

What the Sample Data Looks Like

Image for post
Image for post

How to add Subtotals to a data table in Excel

1. (Optional) Ensure the first row are column headers

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)

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:

Image for post
Image for post

3. Sort the data

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.

Image for post
Image for post

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.

Image for post
Image for post

Your data should now be sorted by the column that you plan to have subtotals on:

Image for post
Image for post

4. Perform the Subtotal

Image for post
Image for post

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.

Image for post
Image for post

b) Now you’ll see the Subtotal window:

Image for post
Image for post

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).

Image for post
Image for post

5. View the Subtotals

Clicking on the “2” will collapse (hide) the details and show what you subtotaled on:

Image for post
Image for post

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

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”.

Image for post
Image for post

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:

Image for post
Image for post

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:

Image for post
Image for post

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!

Image for post
Image for post

Some Last Notes

Subtotals Within subtotals

Image for post
Image for post

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:

Image for post
Image for post

And of course you can do the fancy highlighting trick; I suggest picking two different colors to make it easier to read:

Image for post
Image for post

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).

Image for post
Image for post
How does this even happen?!

Which leads me to the last tip:

How to Remove Subtotals

Image for post
Image for post

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.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store