PivotTables are a great way to summarize, analyze, explore, and present your data. You can even group the data in a PivotTable to enhance the layout and format of your PivotTable reports.
In the PivotTable, right-click a cell with a date in it, and then select Group.
In the Grouping box, select the Starting at and Ending at checkboxes, and type or edit the start and end date, if needed.
Under By, select one or more options to group the dates.
We're looking at a PivotTable on a worksheet called, "Date Grouping." The Pivot Table field list is off to the right.
Right now we're showing Date. We'd like to be able to group this data by maybe month, or year, or quarter, something like that.
All we really need to do is right-click one of these dates.
Now, before we do this, recognize that the date here is in the row labels area of this Pivot Table.
We see the check mark up above. There's the Date field name.
So, we'll right-click one of these dates over in column A, any one, and choose Group.
The 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 Pivot Table. Immediately, it's very compact.
It's showing the breakout by months. This might be all we need to see here.
However, those Januaries do represent two different Januaries and that's not wrong but, on the other hand, we may want to have this broken out by year, and possibly even by quarter.
So, what I could have 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.
Now we're seeing the data broken up by years, and quarter, and date.
Notice in the Pivot Table 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, it should say Months.
So, we can just click here on cell C4 and retype Months.
As I do, keep an eye on the Pivot Table 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 Pivot Table 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 have wanted.
What if you wanted to make a presentation and preferred to just 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 here now shifts to the data here and we can see the breakout this way.
There are those times when you want a smaller display of the data, where there's not too many numbers at once here.
While you're looking at this, I'm gonna try something that might not have occurred to you.
In the row labels area of the Pivot Table Fields list off to the right, I'm going to drag Years and put it under Quarters.
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.
It is not uncommon to want to be comparing quarterly totals from one year with quarterly totals from the previous or the next year.
So, we see those two next to one another.
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 did with Quarters, and we see this layout.
Now, maybe it's a bit busy because of the subtotals.
We can go to the Design tab in the Ribbon, left-most button, Subtotals, Do Not Show them for the moment.
Now we've got our two Januaries together and, there too, sometimes that kind of comparison makes sense.
Now, what we also have in play here is that 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 quarter one. I'll collapse quarter two.
Now we see the breakout for quarter three and quarter four for both years.
The major difference between the report layouts Outline and Tabular is, first of all, they have the same general look except the Tabular has grid lines.
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. Well, there they are.
We also got them for the quarters, too. Subtotal for 2014, 2015.
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.
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.
Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.
Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.Benefits
Get unlimited access to over 4,000 video courses.
Receive personal recommendations based on your LinkedIn profile.
Stream courses from your computer or mobile device.
Take courses for every level – beginner to advanced.
Practice while you learn with quizzes, exercise files, and coding windows.
Choose a plan for yourself or your entire team.