Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Earlier in this course, we talked about several methods of adding numbers. And we also talked about different ways of sorting and filtering. In this movie, we'll put these concepts together to let Excel create subtotals for us automatically. If you take a look at this worksheet, we have some pretty typical information. Last Name, First Name, Department and so on. Well, let's just do some basic sorting first. Click somewhere in the Last Name field. And on the Home tab, let's go over here to Sort & Filter, and we can sort A to Z. Maybe let's go to State.
Click Sort & Filter and we can sort Z to A. Well, what we want to do is we want to give some information to department heads. And you see we have all these different departments. We want the department heads to know, for example, what's the total number of hours that their people incurred, and maybe we want to tell them what's the average rate. So first thing, you want to do is go into the Department field and let's Sort & Filter A to Z. So we have all the executives, all the finance people, all the graphics people and so on.
So now go up to the Data tab and over on the right click Subtotal. So what we want to do is find where is this changing. Well, we're changing this by department. So over here where it says At each change in, click that drop-down and choose Department. Well, what function do we want? Well, we want to add up the hours. So click on this drop-down and choose Sum. And what do we want to sum? Well, we want to sum the hours. So over here if you have anything selected, just deselect. Scroll if you need to. Now, we want to make sure that Hours is the only field that has a checkmark. And that's it.
Click OK. Now, we can see total hours for executive, total hours for finance, total hours for graphics and so on. You might also notice over here, we have the outlining as in effect. And if you watch the movie on grouping and outlining, this will look familiar. And here's the deal. If you click this number 1 on top, this shows you just the grand total. Click the number 2 and this shows you grand total plus the component totals. If you click the number 3, this shows you all the detail, plus-- I'm just going to click the number 2 here for a second.
You could expand or collapse any of the components. So maybe you want to see just the Finance department detail and the aggregate detail for all the other. So it's very flexible. Well, let's click on this number 3 so we can see all the detail again. And now what we want to do is we want to display what is the average rate for each department. So make sure you click somewhere in this data area. Any cell weill do. Go back to Subtotal. And again, we're going to keep At each change in Department. But this time, we want to use the Average function.
Deselect hours because we're not going to average the hours. We want to average the rate. And you see over here where it says Replace current subtotals? We want to deselect that, because we want to leave the totals here. So we're going to have Total and Average, Total and Average for each department. Then click OK. So now we have the average executive rate and the total executive hours. And we have the average finance rate and the total finance hours for each one. And now you see over here in the outline we have four levels.
So if you click 4, we have all of the detail. Click the level 3, we have the aggregate, average and total for each department. Click the 2 we have just the totals but Grand Average and Grand Total. And if you click the number 1, we have the Grand Average and Grand Total for the entire worksheet. And I'll just click this number 4 again. So if you ever need to find subtotals or averages or counts for a large amount of data, don't do them manually. Use the Subtotal feature in the Data tab. I think you'll find it pretty handy.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 99233 Viewers
80 Video lessons · 141825 Viewers
59 Video lessons · 60201 Viewers
52 Video lessons · 73317 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.