Use the Subtotal command to analyze data with single- or multiple-level subtotals

- [Man] When you're working with large lists, you might want to insert subtotals, based on break points within the data. Now, on this particular worksheet here, the data's not quite in the order I want, but here's what I'm envisioning: I've got about 20 or 25 departments in this list, goes to about 700 rows. I'd like to see a subtotal inserted automatically at the end of each department. So I want this list sorted by department. Some of the departments are quite large, so I might want to have a secondary set of subtotals as well, maybe by status, or maybe even a third level, possibly by job rating.

So the first order of business is before using this feature, and it's found on the data tab, way off to the right, and notice that it's in a group called outline. If you're not familiar with the outlining concept, you're about to see it being used as we use the feature called subtotal. Now we can use multiple layers here, so I've called this workbook subtotals as a reminder of that. But the feature itself is singular subtotal, but let's first do the sorting. If we don't do that, the feature's not very valuable. The data's structured already, it has no empty rows in it.

I'll jump right into the sort command here. I want to sort on three levels, I want the major sorting, the major gripping to be by department, and within that, by status, and within that, let's say by job rating. And if it's job rating, I want the highest job rating, number five, to be first so I'll put largest to smallest in the sorting. These are grouped by department within each department, they are now ordered by status and within each common status they're in descending order by job rating. Let's now introduce a subtotal row at the end of each department.

On the data tab, far right, outlining group, subtotal. Every time there's a change in, the department, we want to see totals. Now don't count on these check boxes being accurate. Sometimes you'll see, for example, job rating. We don't want to be adding job rating. You can use different functions here, but only one within the same row and sum is likely to be the most common for most people. We want to tabulate the total number of years and the compensation, but not the job rating.

Click okay, and we got our totals. And there it is for ADC department, here's the one for admin training, let's see what's happening off to the right, total number of years of service, total compensation. But off to the left, what do we see? Outlining symbols. Right now the number three is selected, it doesn't look like it, but I'm going to select number two by clicking it, and what do we see now? Just the subtotals and the grand total at the bottom. If we click one, we're only seeing the grand total.

Not so valuable. Two, back here again. If you were making a presentation of this data and someone asked you about details, say for one of the groups, how about the audit services group here? Click the plus just to the left of it, that expands that group for the moment so we see the detail. When I close it, here's the minus to collapse it. So that's valuable also as a presentation tool. If for a while, you know you're not going to be expanding and collapsing, you can hide the outlining symbols with ctrl, eight.

We're not seeing them anymore. You need to get them back? It's ctrl, eight again. Remember, to see all the data, click the three. Now suppose we want to have a break point within this. Earlier, we did sort, and for example, an admin training, the two contract people, all the full-time people. We see how it's sorted within each department. Let's introduce another level, subtotal again. This time, every time there's a change in status, we want a row of subtotals.

For the same entries here. Make sure this box here, replaces current subtotals, is not checked. You want that to be empty. Click okay, and now I've got subtotals within subtotals. So for example, here's the admin training group, right here. We got a contract total, full-time total, half-time total, hourly total, just down to there. I'll zoom back just a bit using ctrl and the mousewheel, so we can see all that at once. There's the entire admin training group. Top to bottom, with this major total down here, actually on row 33 there.

And the other subsidiary totals. And since we did sort these at a third level, we can have a third level. Notice before we do that though, we now have four outlining symbols to the left. So clicking the number three now collapses the data to show both levels of subtotals, and the grand total at the bottom. So we got more possibilities here. But not that it's absolutely necessary, but since the data is sorted, and we don't necessary have to show the detail, but let's do anyway. So now let's have an additional level of subtotals here.

Subtotal, at each change in, job rating. Once again, we're not replacing current subtotals, but another level. And this is probably more than most people would want, and I think you can see in the example here, total number entries' about 700, but at three levels here, this is looking a bit scattered, a bit fragmented. So there's the contract total there. We got one person with a job rating of five, one with three. So in a list like this, this is probably a bit too much.

I know from the experience of working with this list, that the manufacturing group has a ton of people in it. Maybe the breakouts there make a bit more sense, here's manufactoring, they're all the fives who happen to be contract. And here are all the fours who are contract. So I think you can sense, based on the data, how maybe two levels, three levels, at some point you'll recognize that maybe there's just too many breakpoints in here. But it is valuable information, there's no question about that. If we want to backtrack here, in other words, if we only wanted to show some of the data, remember now if we click four, the number four, I've got five buttons up here now, We're seeing all three levels of subtotals, as well as the grand total.

Moving leftward here, clicking three, we're seeing just two levels, clicking two, we're just seeing one. Now in all these cases too, if you wanted to copy this data and put it on a different sheet, suppose you were to highlight all this, maybe before doing it, say let's hide the columns here, we don't need to see that, right-click and hide. What if we wanted to copy and past this information? And we don't need to see benefits either, let's right-click that column, hide, let's copy this to a different sheet, what happens? Ctrl, C, go to a different sheet here, ctrl, V, we get all the data.

So what do we do here? If this were a filtered list, we wouldn't worry about that, but this isn't filtering, this is using subtotal. In cases like this, you want to remember a keystroke shortcut, and I'll also show you the command way, alt, semi-colon selects the visible sales only, then do the copy, ctrl, C, and go over to different sheet here. I'll past this right next to the other data, ctrl, V. And that's looking a lot better. We're only seeing the visible data, we're not seeing those extra hidden columns, nor all those rows of data.

I only want to see the visible data. So anytime you're working with a subtotal list like this, and I'll press escape here. When you're seeing a view like this and you're ready to copy it. If you highlight the sales and then press alt, semi-colon, you will select the visible sales only, and by the way, if you happen to forget that command, good luck trying to find it, but here's where it is. So on the home tab, far right button, find and select: go to special, and there it is, visible cells only, okay.

Remember, the keystroke shortcut, a lot faster, is alt, semi-colon. Press ctrl, C to copy and then do the paste. This time it'll be redundant, but never-the-less, ctrl, V. So this is a powerful tool, there's no question about this. Let's say if we no longer want this feature, in other words we might have clicked five or four along the way, we're seeing all the detail again. Let's bring back all the hidden columns, you can right-click and unhide all those.

If we want to see our data in its more standard look, clicking five here shows us all the detail, but all the subtotals as well here. This is not what we want. Eventually, at some point, we want to get rid of these. Back on the data tab, far right button, subtotal, let's just remove all. By the way, there's one other ascept of this you should be aware of. Here's the initial list. If this list has been converted to a table, you cannot use the subtotal feature. One the hometab, for example, there's a choice out there for formatting this as a table on the insert tab, you can see it here as well.

But no matter how you convert data to a table, once this is a table, if you go to the data tab, the feature is not available. You cannot use subtotal along with a table. Other than that, when the data's not a table, and I think you've seen the advantages of how we can use this to give us those subtotals based on data in a list.