Join Dennis Taylor for an in-depth discussion in this video Inserting subtotals in a sorted list, part of Excel 2016 Essential Training.
- We're looking at a worksheet called Subtotals in the workbook 12 - Data Management Features. As we scroll up and down here we can quickly see this list is sorted by Department. And you might not notice it's also sorted within that by Status. We might want to insert subtitles every time there's a change in the department. We've got lots of different departments represented in this list. Doing this manually would take quite a while. And since we've also got it sorted within that by Status we might even want to have a multi-layered set of subtitles here.
We can do this efficiently with a feature called Subtotal. It's on the Data tab on the far right. You must sort the data ahead of time if you expect coherent results. So let's say this example here it's been done. Active cell can be anywhere within the data, we go to the Subtotal button, Data tab in the outline group. Every time there's a change in what department. Now often this might be Employee Name because it's the first column. If you've used it recently, maybe use a different name.
Make sure it's what you mean it to be. Every time there's a change in department we want to automatically insert a subtotal. Use the function, probably Sum, but not necessarily. We want to be tabulating probably the totals of fields, but in some cases it might make more sense to do Average. What we would like to be able to do, but can't do, is Sum certain fields, and Average others. So pick Sum is likely to be your best choice. Now which fields might we want to total? Excel makes some guesses here, often gets them right.
Years, Compensation. We wouldn't want to do Job Rating, because a total there wouldn't really mean much. Farther down, New Comp. Now if you've used this recently as I have, you might see checks in the correct place, but always check it out and possibly consider some other fields, as well. And click Okay. As we start to scroll downward, we will see eventually, as soon as we come to the end of Account Management, there's a total. Now because we haven't frozen the top row we're not seeing right away what this is.
This represents years of service, this is the compensation, and here's the adjusted compensation. We're seeing totals for these three fields. Here's the ADC group, much smaller group, smaller totals, and so on. All the way down the list. Also of note is on the left hand side of the screen we see what are called outlining symbols. The horizontal one, two, three. The current view is showing detail and Subtotals. That's level three. Button number two and we're seeing Subtotals and Grand Total.
Click one we're only seeing the Grand Total. So we can do this back and forth, comparing these. If you're doing this for a presentation, and someone in your audience is looking at this, and wants to see some of the detail, for example, in the Environmental Compliance Total, or the numbers behind this, there's a plus off to the left here, click this. And now we see the detail for that group, at least for the moment. All right, we see it, we view it, maybe we talk about it, we collapse this with the minus button off to the left here.
We're back here, so we can expand these individually, if we wish. Now the list also was sorted by Status within each Department. We could also, without getting rid of these, introduce another level, provided the sort had been done ahead of time, and it had been, Subtotal, let's go back and also have a Subtotal every time there's a change in Status. Now let's say we want the same kinds of totals here, but be careful, be sure and uncheck Replace Current Subtotals if you wish to see both levels.
Right now the check is there meaning it would replace the current ones. We still want to see those, but let's also see the news ones, as well. So we're going to see a multi-level here of subtotals. Click Okay. And now we see. In Account Management now we see that total right here, we see the breakout, we can make the column wider here with the four different status types. Now some departments don't have four status types within them. So we'll see, for example here, only three. In some cases only two.
But we are seeing the breakout here. Remember, the data originally was sorted, and we can see it again by clicking in those, we now have four levels on here, clicking level four. That's all of the detail level, as well as the various subtitle levels. So there's the subtotal for Contract within Account Management. And then Full Time within Account Management. And then Half-Time, and also Hourly. And there's the Grand Total for Account Management, and so on. Now if you use this feature often I think you'd recognize that if you like to sort your data frequently, if you need to do that if you're trying to filter data or if you're going to create Pivot tables off of this, these are in the way and you need to get rid of them.
So you might think of it as a feature you use from time to time. That's easy to create once you try once or twice, I think you'll see how fast it is. But there's something else you might want to consider doing here. I'm going to collapse this to show just the various levels of subtotals. And we can see by the row numbers here, and of course we know from the visual, we are not seeing all the data, and that's fine. But you might want to copy this, either in its current form, or maybe if we collapse this even further by clicking the two of those outline symbols, number two.
What if you wanted to copy this to a different worksheet? I'm going to zoom back a bit, highlight this, you might want to copy it. Now we don't have an empty worksheet right now so here's a plus button out here to the right, I'm going to click that. That puts in a temporary new sheet to the right called Sheet One. Let's go back to our data. If you highlight this and copy it, you could press Control C, or right click, Copy. If you then go to that new sheet, or any other location, and paste, Control V is one way, you could also do this with right click, or on the Home tab we can do a paste.
And we say, well, wait a minute here, looks like we're getting everything, and that's what happens. Now you could imagine wanting to get just the visible data. And there is a way to do this, although it's fairly obscure as to how to find it. There is a keystroke shortcut, it's Alt semi-colon, but if you didn't know that it would be tough to find it. On the Home tab in the ribbon, far right there is a button called Find and Select. And buried in there is a feature called Go To Special.
And in there there is a choice called Visible Cells Only. They couldn't have buried it any further, I don't think. Click Okay. Now that selects just the visible cells. Then if you were to copy, right click, Copy, Control C, it's only going to pick up each row, or each portion of row, individually, and the visual sort of suggests that. So then if we went to that empty sheet, or the sheet that was empty, and paste this off to the right, you'll see by comparison it's going to be much shorter. Control V, there we go.
Now we see what's happening out there. Now let's go back to subtotals. Now I'm not necessarily suggesting this is a feature that everybody needs, but it is contrast with data that you work with if you're using a filter. And you might not have seen the movie on filter just yet, but if you do filter data you don't worry about this feature. If you copy, paste, filter data, it automatically picks up just the visible data. So once again, in this list right here. If you'd like to copy this and paste it as you see it, without picking up the hidden data there, simple highlight the data, either press Alt semi-colon, not easy to remember exactly, or go to Find and Select, go to Special and select the Visible Cells Only.
Then copy and paste the data if you only want to get this data. Now going back to our data here, remember, we can display the detail this way, or more detail if we've got two levels we can do it this way. At some point if we no longer want to use the feature for awhile, we'll simply go to the Data tab, Subtotal, off to the right. Sounds drastic, but Remove All simply means remove these subtotals, and we're back to here. Try this a few times, you realize it's relatively fast once you get used to it.
It does give us some good, quick summary information. It's called Subtotal on the Data tab in the ribbon.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros