And the Subtotal Is…
Have you ever found yourself working on an Excel worksheet desperately trying to group similar data, insert a row or write a formula for the group?
How was it?
What was that you said?
Did I hear you mutter under your breath something about it being a complete pain?
Yep, that’s what I figured I’d hear. (I thought the same thing too).
So, the question becomes (after you’re done muttering about it, of course) what can we do to make it better?
In the best of worlds, there would be a way to get Excel to do all the work. You know, no more inserting lines, writing endless formulas, making sure you get the right cells into each formula, etc.
Well, welcome to the “best of worlds,” because that’s exactly what we’re going to get Excel to do for us today.
Ready to get started?
Good, then let’s get on our way.
The first thing you need to do is sort the data on the field that defines the groups. (Maybe a person’s name, a date, a part number. Basically, whatever defines a set of items as belonging together). You can use secondary sorting criteria, but be sure the primary sort field is the one that defines the groups.
In my example data, I’ve got four different sales reps and some sales data.
It’s obviously listed by date, but what if I wanted to know how much each rep has sold?
To get a total from this data, as is, would be a nasty mess, so the first thing I would do is sort the data by sales rep.
This is better, but I still don’t have the sales subtotal for the reps.
Here’s where we could do it all by hand (insert a row, write a formula for each rep), but the goal here is to avoid all that manual and repetitive work.
So, let’s get Excel to do the real work for us.
With our data sorted, select any cell within the list. Now, go to the Data menu, Subtotals choice.
The Subtotals window will open giving you a wide variety of options.
In the first field, “At each change in,” select the data that defines the groups. This tells Excel which data changing should trigger a new subtotal. (If I were working on sales rep totals, I’d pick that from the drop down list. If I wanted to know daily totals, I would have sorted by date and selected a date from the list. You get the idea).
The “Use function” field is a list of functions you can have Excel display for each group. Pick whatever subtotal data you were looking for so Excel knows how to write its formula. (Take a look at the list. You’ll find that you have many more options than just a sum in the subtotal window).
The third field, “Add subtotal to” tells Excel which data to perform the function on. (My example would be for “Sale Amount” to get the total sales for each rep).
At the bottom, you’re given three more choices. Check any box you want Excel to apply. These are pretty self explanatory, so I’ll save you the long winded description, but you might want to note that you can get Excel to give a grand total, as well as, subtotals by ensuring that the “Summary below data” box is checked.
When you’ve made all your choices, click OK.
Your data is now beautifully separated into groups with subtotals automatically inserted between them.
(Yeah, I know! That was way faster than the do-it-by-hand, one group at a time method).
If you change your mind about the subtotals, just immediately hit the Undo button.
Should you decide later that they must be removed, return to the Subtotal window and click the Remove All button.
Also, if you decide that you want a different type of subtotal (maybe an average instead of a sum), you can return to the Subtotal window and change the “Use function” field. Make sure the “Replace current subtotals” box is checked to make the change.
Ah, did you catch that?
You have to check the “Replace current subtotals” box to change the type of subtotal, but what if you want a sum and an average subtotal?
Create the first subtotal, then go back in to create the second. Before you click OK on the second subtotal, uncheck the “Replace current subtotals” box.
Take a look.
Now you’ve got two different pieces of data on each group and two different grand totals at the bottom.
Now you’re ready to go subtotal crazy!