Join Dennis Taylor for an in-depth discussion in this video Grouping by other factors, part of Excel 2016 Essential Training.
- In addition to Grouping data by Date in the Pivot Table you can also come up with your own Groupings. We're looking at a worksheet called OtherGrouping it's in our workbook called 14 - PivotTables. And we've got some data here and we got our Region broken out, Northeast, Northwest, Southeast, Southwest. And someone is asking us, "Can you come up with a Total for the East?" "Can we see this in the PivotTable?" Well, we don't really have any Eastern Region. We have the Northeast and the Southeast. First of all, let's put these together. Now, we don't need to highlight this data here if we want to move it next to Northeast, all we need to do is to select its label and then drag the bottom edge leftward.
And the numbers below that, the 284, the 214, and so on will follow this as I drag just that Cell that contains the SE. Drag it over here to the left, let go of the left mouse button. And now they're adjacent to one another. We can highlight these two Cells right here, the NE and the SE, right click, and Group, so it introduces a new Row. Here's that Group name, somewhat bland. We'll just type in East, much more sensible name, and Enter. Notice there's a (-) minus in front of it that suggests we can collapse it.
And over on the right, why not do the West as well. NW and SW, same idea, right click, Group, and now we see Group2. Let's change that name, just click there and type West, Enter. Now before collapsing, look at the data. There's a 2807, 1243, this is going to go over 4,000 about 40, 50, or so. We can collapse the East by double clicking East or clicking the (-) minus, either way, there it is. Same thing with the West. All we got to do is double click or click the (-) minus, there we are.
So we're seeing essentially new data. Now it's based on our source data of course and these Totals are the same as the four Totals that we saw earlier, at least they add up to be the same. But we now have this ability to expand and collapse as we wish. And we got a new name up here called Region2. And over in the PivotTable Fields list you'll see Region2. That's not exactly very explanatory and can't immediately come up with a great name here but how about East, West, Breakout or E-W Breakout something like that. Something that you'll remember it by.
Something like that maybe. Now when these are collapsed and expanded of course we can see these different views. In the PivotTable Fields list, and also notice in the lower area here, we've got two Fields in the Columns area. If we don't want to see Region for the moment I'm going to temporarily drag it into the Filters area here. We now see East and West without the ability to expand and collapse. And sometimes that might be okay, sometimes not. But we can always bring a Region back in here. Or if we don't want to use that Breakout for a while either take it off the Grid completely out this way or put it in the Filters area.
So we've got some flexibility with this. And we could use the same kind of feature on Rows. Both of these items are Bamboo, they're both Tables so we might crib this under a name like Table, or Bamboo. Or since maybe they're living room items we could put an LR or something like that, come up with our own name But the point is we can Group these, right click, Group, there we are same idea. Instead of Group1 I'm going to call that Bamboo Tables maybe. We only have a few products in our list anyway but just to bring out the idea that we can also Group Fields here as well.
I'll put in LR, meaning Living Room Tables. And we don't necessarily need to do this with the other items. But maybe these two, these are Living Room Furniture also. I'm not sure of a good name there but same idea, right click, Group, and there we have a Group2. And I'll just call it LR Furniture. Not quite the best naming convention but it'll do for now. And we have the ability, of course, to expand and collapse here too, clicking the (-) minuses. So a different kind of list. And you can imagine with different kinds of data how you might want to do that. If you had peoples names up and down here, maybe the Salespeople like we'd seen earlier.
We could certainly bring those into the mix. I'l click Salesperson, it becomes a Label out here, in the Row Labels area. And the others for the moment, oh, move into the Filters area. Some of these Salespeople might work out of the same office maybe some do, some don't. We can move them up and down, Group them in the same way. So maybe Susan here works with Meg and they're out of the Chicago office so we could take that and the data will follow it as we drag it up here. Those two were together maybe we could highlight those, right click, and then call that Chicago, if they're in the Chicago office, and so on.
I could imagine doing that with some of the others as well. So you have this ability not only with date as we saw in a prior movie but with any data you can Group it on your own terms.
- 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