Setting up single and multiple-level subtotals

- [Voiceover] In this worksheet called Subtotals we've got a list, and you can probably tell it's sorted by Department, and within each department it's sorted by Status. There's even a third-level sort there, more difficult to see. Third-level sort is by Job Rating. Now, we'd like to be able to get some totals for each department, and not too many of the fields actually are ideal for totaling, but we might want to know total years of service, total amount for compensation, or possibly do an average. We can't mix the two, in the feature about to show you, it's called Subtotal. It's found on the Data tab, far right, it's in the Outline group, Subtotal. Let's have Excel automatically insert a subtotal every time the Department changes. Active cell can be anywhere within the data. We'll click Subtotal. At each change in:, now it's not the employee name where we wanna see, a new line inserted, after every change there, 'cause that's every line, At each change in: Department, so, we're about to see a subtotal line between ADC and Admin Training. And at the end of every department, whenever there's a Department change, we want to see a total. The function we use is likely to be Sum, there's certainly some other options, unfortunately we can't mix these. So for example, in the data in the background there, we might want to know the average years of service, and average compensation, or possibly average years of service and sum of compensation. We can't mix them, it's got to be all of one. So let's say we use Sum. Let's do that for Years of service, also for Comp., nothing for Job Rating. Click Ok. And every time there's a change in Department we've got a total here. A total for the years, a total for the compensation. So we start to scroll up and down the list, in some cases we'll see pound signs, so we might see that in column G as well too. So let's highlight those three columns and double-click any boundary out there between the column letters to readjust those widths. So for Admin Training for example, 186 Years of service, 1.13 million is the current compensation pay. Same as highlighting these numbers, we'd get that same answer there. And we see that for department after department. So Excel does all the work for us. And we don't always need to have a secondary set of totals, but since the data was sorted by Status, and this is more obvious as we look at Admin Training, that's a department that has lots of different people working for it. Let's have a total for Contract within there, and also for the full time people within Admin Training. Let's go back to Subtotal. This time At each change in: Status, every time the Status changes. That was our secondary sort. Same fields we want to have subtotals for. But we do not want to Replace the current subtotals, so uncheck that box, then click Ok, and now we've got two layers of subtotals here. And zooming back a little bit, we see the data, possibly readjusting the column widths, this way, double-click, and while we're at it, doing this too, we might want to hide certain fields that we're not tabulating. That's not truly necessary, but we could start by dragging across columns E and F, the Hire Date and Months, then using the Control key, click Benefits, maybe column J as well too, and column C. And right-click and simply Hide those. Cleans up the display a little bit. So now maybe we're seeing this a little bit better. Here's all the data for Admin Training, I'm highlighting it right here, we see what's happening. I scroll up and down a little bit to see that better. Not only are we getting the Admin Training total, but we're getting the total within that for the hourly people, the half-time, full time and contract people. Now, how often would you use this? If you use it frequently you want to think of it as something you'll use and then stop using it. Because although we can see totals nicely here, we can't work with the data on a regular basis. In other words what if we want to sort this data, if we want to add new records, if we want to do filtering, if we use some other features here, pivot tables? None of those are going to work very well, particularly pivot tables, if we leave the data in this state. So the idea is we see this, we use it, we print it, we copy it, we do other things with it, and then in the next movie you'll see how to use those outlining symbols as well. But when we no longer need the feature, we'll simply disable it. We go back to Subtotal, and choose Remove All. Sounds drastic, but all it's doing is removing those subtotal rows. And we're back to normal as soon as we do this. In this case because I did hide the columns we'll need to Unhide those as well too. That wasn't truly necessary but something you might want to consider doing when you are viewing subtotals. Drag across these columns here, there, and simply double-click one of the boundaries or more logically maybe right-click and Unhide those columns. So, it's a great feature for getting the subtotals that we need. And in the next movie you'll learn how to use the collapsing and expanding outlining symbols that come with subtotals.