Join Dennis Taylor for an in-depth discussion in this video Grouping by date and time, part of Excel 2016 Essential Training.
- We're looking at a PivotTable on a worksheet called DateGrouping. It's based on the data in the Creating sheet in this workbook, and if you saw prior movies in this series, you recognize the data. The PivotTable Field list is off to the right, and right now we're showing Date, and that doesn't look very promising. More or less PivotTables seem to be compact and concise, this one certainly isn't. We're covering data over a two-year period, and this just goes on and on and on. It's a bit shorter than the source data but still, not very compact.
We'd like to be able to group this data by maybe month, or year, or quarter, something like that. And all we really need to do is to right click one of these dates. Now, before we do this, recognize that the date here is in the Row label's area of this PivotTable, you see the checkmark up above, there's the Date field name. So we'll right click on one of these dates over in column A, any one, and choose Group. A prompt comes up saying Months, but below that we see Quarters and Years. Let's just start with Months, we could choose all three, or any two, or any one, we'll just choose Months. Watch the PivotTable.
Immediately it's very compact. It's showing the breakout by months. This might be all we need to see here, however, those Januarys do represent two different Januarys, and that's not wrong, but on the other hand we might want to have this broken up by Year, and possibly even by Quarter. So what I could've done the first time, and I'll do it now, I'll right click, but now we're right clicking on one of the monthly names. Go back to Group, and this time click Quarters and Years, you don't need to use the ctrl key here, we can select these as we need them. Click OK.
And now we're seeing the data broken up by Years, and Quarter, and Date. And notice in the PivotTable Fields list to the right: Years, Quarters and Date. The word Date, for the moment, is slightly off. To be in sync with the other ones that should say Months. So we can just click here on cell C4, and retype Months. And as I do, keep an eye on the PivotTable Fields list to the right. Down at the bottom here where you see Date, that will turn into Months as soon as I type it. Enter.
There it is. And even in the PivotTable Fields list it says Months. The original data is not going to say Months, it's still going to say Date. So, we've got three levels here of Date information, now, possibly, that's more than we might've wanted. What if you wanted to make a presentation and prefer to start off with just showing Years and Quarters? We can take Months out of here, but rather than eliminating it, let's temporarily put it into the Filters area. Now, in addition to making the list more compact, it also shows above the list an area up here.
Now, I don't think that would bother anybody, but that's up there because it's in the Filters area. But the focus now shifts to the data here, and we can break out this way. There are those times when you want a smaller display of the data, there was not too many numbers at once here. And while you're looking at this I'm going to try something that might not have occurred to you: in the Row label's are of the PivotTable Fields list off to the right, I'm going to drag Years and put it under Quarters. And now we see this layout. And that's a bit unusual, but on the other hand it's valuable because it's showing the two first quarters together, the two second quarters, and so on, and, it's not uncommon to want to be comparing quarterly totals from one year with quarterly totals from the previous or the next year.
And so we see those two next to one another. And in a similar way, if we were to take Quarters out of here, I'll move it into the Filters area again, and then bring Months into the Row labels area, we see this list that's a fairly standard looking kind of list, but here too, I'm going to put Years below Months as we do with Quarters, and we see this layout. Now, maybe it's a bit busy because of the Subtotals. You can go to the Design tab in the ribbon, leftmost button, Subtotals, do not show them for the moment.
But now I've got our two Januarys together, and there too, sometimes that kind of a comparison makes sense. Let's compare this January with last January or next January, that sort of thing. So we can get some unusual layouts here, and the goal is certainly not to try all possible layouts, because there're quite a few. Imagine moving these back and forth, different combinations. So, first of all, for certain situations, maybe the printed page, or even certain presentations, Years, Quarters, Months makes sense. Now, what we also have in play here is the idea that with multiple fields in the same group, in other words, the row labels area here, two of the fields are called outer fields, you don't see that in the menu system, but the outer fields can be collapsed.
For example: we're looking at this data, maybe for the moment the focus is pretty much on the year 2015, of course we could scroll up and down, but since 2014, which is a Year, is an outer field, we can double click it to collapse it, or click the minus, either way. So that collapses into a single row, while we shift the focus to the year 2015. We can bring this back, of course, by double clicking, or clicking the plus. Another thought too, we want to shift the focus to quarters three and four for both years.
I'll collapse Qtr1, I'll collapse Qtr2, and now we see the break out for Qtr3 and Qtr4 for both years. There are other ways to do this as well, too. I'll bring that back again, this time by pressing ctrl + z a couple of times. Another option here, slightly different, but the same general idea: we could go to the filter arrows here, and not show Qtr1 and Qtr2. Slightly different now, we don't even see that there's a Qtr1 and Qtr2, of course we know that, but we are seeing the detail for Qtr3, Qtr4, in a slightly more compact way.
And, in a similar way, if we don't want to see 2014 at all for now, we could click the drop arrow next to Years, right there, and unselect 2014. Now if there's any data before it, we don't want to see that, looks like there's none in the example here. So we see this data too, this way, just 2015. But we know full well we've got the other data as well. So in varying ways these filter buttons can be used, or that ability to quickly expand and collapse by either double clicking or clicking those minuses and pluses. So once again, reversing stream here with some ctrl + z, bring back the data this way, the Quarters, and now we're seeing both years as well here, too.
Now, another feature here that might get your attention at times, and it can be a little bit misleading, in the first movie of this chapter I indicated that with Report Layout you could use Compact Form. This has little meaning until you've got multiple fields like we do now. Think of how wide this list is right now. It goes out to column H, but you see how much screen space it takes up. I'm going to show this PivotTable in compact form. It takes up less horizontal space. Now, you might or might or might not like the display of this, I'll press ctrl + z then ctrl + y, we can compare the two.
Sometimes it's advantageous to have a more compact view. One of the downsides of compact view is you're going to have to manually change these labels, or just not show them, and that's why I prefer the other two views. The major difference between the Report Layout's outline and tabular is, first of all, they have the same general look except the tabular has gridlines. But there is another difference, and if you encounter this at the wrong time, it might be somewhat confusing. Let's say that I want Subtotals here for the Years. Subtotals, Show all Subtotals at Bottom of Group.
Oh, there they are. We also got them for the Quarters too. Subtotal for 2014, 2015. Well how about the other option? Subtotals, Show all Subtotals at Top of Group. What happens here? Nothing. Nothing changed. Why is that? You wouldn't know this, except from trial and error, Tabular Form does not allow us to put titles on top, but Outline Form does. Now, I made the selection already, I said put these on top, watch what happens. Now they are on top, here's the 2014 total on top, not on the bottom.
That can be a little confusing at times. And, of course, you'll have to decide whether you want Subtotals, but you will have the choice of putting them top or bottom if you are using Outline Form. If you use Tabular Form, remember that's the one that shows the gridlines, you can only see the total, Subtotals that is, on the bottom. So that's another variation. This grouping capability by Date, let's get back to that idea, also has another advantage to it as well. Right now we're seeing Years, Quarters, Months. I'm going to right click one of the Months again, and go back to Group, and take out Months, and Quarters, and Years, and group these by Days.
But not just a single day, how about 14 days? Based on the idea that maybe in this organization they tabulate sales for the various managers on a two week basis. So now we're seeing the data every two weeks. Now probably what you would want to do is go to a calendar and figure out, first of all, is your two-week basis, does it start on Sunday? And if it does you'd want to make sure that your starting date is, so we could right click one of these, go back to Group, and in 2014 the first Sunday of the month was the fifth.
So I could start this at the fifth, and I'm going to put in 2014 here. Fifth. There we are. And it looks like it goes to the end of 2015, every 14 days. So now we've got our data there, starting at the fifth, so these are two-week periods here, and you can see this. We've got about 50 rows or so because it covers roughly a two-year period. So, some valuable information here, and different ways of clustering the data. We could do this on a three-week basis, or a ten-day, or a five-day, or anything that seems appropriate for the data at hand.
Remember, right clicking here and choosing Group gives us all kinds of options. If we had a transaction file that showed times of day, we might even want to group data by hours, and thereby show the busiest hours of the day, throughout the year, or throughout a two-year period, something like that. So lots of different grouping capability here. The ability to group information by date is a really strong feature, and very easy to get to. And one more thing to look out for here. If the original data has a bad date in it, sometimes you'll do a right click and you cannot group these, and the message doesn't give you a clue as to where that might happen.
So be wary of that. The source data has to be accurate, in other words, all dates have to be valid. So if you've got something like November 31st, or April 31st, a day that doesn't exist that's somewhere in the data, you're gonna have problems trying to group these. So correct the dates if you run into that problem. Otherwise, I think you can see this is a great feature.
- 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