Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet called DateGrouping, we see a Pivot Table with the date field in the Row Labels area and this is not a very compact Pivot Table. There are no restrictions on how big a Pivot Table might be, but this is not much of a summary hare. We're seeing the breakout day by day over a two-year period on any day where there was any sale. Let's compact this. If we have date entries and all of our date entries are valid, we can simply Right-Click on one of these date entries and choose Group.
Excel recognizes the data as date typed data. It suggests possible months. Let's go with that and see what happens, click OK. And immediately, we're seeing a summary by months. Now, remember this is a two-year period, so this might not be quite what we're aiming for and yet you can see pretty quickly how this summary is practically instantaneous. We've got totals by month covering a two-year period across the different regions here. Let's Right-Click on one of the months here, chose Group again. Earlier when we saw this list we could have grouped by quarter and years as well.
You simply click on the additional fields you might need. So, we might choose any two of these or all three of them or just one of them. Recognize also that if we were looking at time data, we might want to group it by hours of the day. But let's take a look at Months, Quarters, Years as we click OK. Now, we see a nice breakout here by Year, and Quarters, and Month. Now, the word Date here is a little bit off. These are months so let's change that and you can do this within Pivot Tables. Let's just call that Month, much better.
And you might or might not have noticed that in the Pivot Table field list we see the word month. Now, the source data still does not contain a column with the word month, but we see that here as we work with the Pivot Table. Now, scrolling up and down a little bit, this list is not that large. It's relatively compact. It goes down to row 29 here, but as we look at this list here and click back within the Pivot Table and see our Pivot Table field list, recognize that in the Row Labels area, we've got Years, Quarters and Month.
Now, what might we do here if we want our list to be a bit more compact? Maybe we're going to try looking at this just by Years and Quarters. So, let's take Month and to keep it handy and nearby, let's put it in the Filters area and now look at our Pivot Table. We see totals by Years and Quarters. Now, let's try something that you might not instinctively try. Flip the order of Years and Quarters within the Row Labels area. So, I'm going to drag Years downward, watch the display now.
That's probably not as common as the previous view, but it does allow us to show for example, our two quarter ones next to each other for 2010 and 2011. Here we see our two quarter twos and so on. So, that kind of a grouping not exactly obvious at first also emerges from this capability, let's put it back again. Let's bring in Month, take out Quarters for the moment. Now, we're seeing Years and Month, and here too might we flip the order of Years and Month? It doesn't seem like the most obvious thing to do.
Here we're seeing our two January's next to one another. Now, when you manipulate date at different times, sometimes you will see subtotals, sometimes not. If you don't want to see them, on the Design tab in the Ribbon, Subtotals, Do Not Show Subtotals. If the view that you have here is by way of the Design tab, Report Layout if you choose Outline or Tabular, sometimes the capabilities of these two are a little bit different. Let's go to Tabular form. You might have seen a choice there under Subtotals, Show All Subtotals at Bottom of Group or at Top of Group.
Now, on the example here, I chose top and yet they appear at the bottom. That's because on Report Layout and you wouldn't know this until you tried it. Tabular form doesn't recognize that choice, Outline does. So, I made the choice to put the Subtotals above and they stayed below. How about show in Outline form now? Now, we see our subtotals above. In other words, here are the totals for two Januarys. So, that's a bit of a change that you might not pick up on at first. If we don't want to see them again, let's just jump back to Subtotals, Do Not Show Subtotals.
And now it's a simpler view. Experiment with these two choices too though and see the differences. Tabular will show gridlines as well and show the display this way. Now, there are a lot of possibilities for changing the way we group Month, Years and Quarters and so although we're not counting the number of possibilities here, just by simply moving these fields back and forth and in different ways, we can get a good quick view of what's happening based on time criteria here over Years, Months and Quarters by using this grouping capability as we see it here.
One other option that wouldn't be so obvious either, if we were for example here, to simply Right-Click one of the year entries and choose Group again and this time turn off these three groupings but instead group these by days. Now, that doesn't sound like it's what we want at first, particularly if we see number of days one. But what if we change that to be 14? What we're now going to get is a two-week grouping and maybe it so happens that in your organization, you group your sales or you tabulate your sales on a two-week basis.
Let's see how this date looks grouped every two weeks. Now, a little bit of fine tuning touch here, you might want to go to your calendar and you probably would want this to start on a Monday or possibly a Sunday so you might want to revisit that and start these on a different date, but you could quickly see we're talking about 14-day periods here. If this covers two years we're about to see 50 rows or so here. So, that's another kind of grouping. If the data had been hours, we could group these by hours of the day to see which hours of the day provided the most sales as well. So, the ability to group information by date and time is a powerful tool when using Pivot Tables.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74854 Viewers
80 Video lessons · 129896 Viewers
52 Video lessons · 64075 Viewers
59 Video lessons · 49876 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.
Your file was successfully uploaded.