Sign in or start a free trial to avail of this feature.
5. Grouping Date Columns
Date columns can be difficult to deal with in a PivotTable unless you know how to use Excel's Group function to format them.
Grouping date columns
- Pivot Tables have an in-built grouping function for handling dates
- This removes the need to create manual 'month' columns in the dataset
- Grouping allows you to create multiple time period fields from one data column
Useful keyboard shortcuts
CTRL + Page Up/Page Down: Move to next sheet
RMBK, G: Open Grouping dialog box
ALT + A, J: Expand entire field
Alt + A, H: Collapse entire field
Analyzing dates in a pivot table can prove problematic because the column entries don’t repeat themselves over time.
Let’s say I add the payment date field to column labels, and I remove the State field.
This gives every individual date a separate column and as a result, we can’t effectively compare sales person performance.
To solve this problem, we need to group these dates by month or by quarter.
One option would be to return to the data set and create a new column that simply prints the month for each date using the TEXT function.
Thankfully, later versions of Excel have given us a better inbuilt solution so that we don’t have to create new data columns.
Instead, we'll return to the pivot table, and we’ll select any of the dates.
I’ll then press the right mouse button key and G for group.
This brings up the grouping dialog box, and allows me to select a time period.
I'll pick months and press OK.
This command groups all the date columns by month and returns the total revenue for each month on our pivot table.
What’s more, I can select two time periods at one time, if I wish.
Let’s return to the grouping dialog box, and this time, I’ll select Quarters as well as Months.
Press OK, and now you can see that we have two rows of column labels for quarters and for months.
If we want to view the quarter totals and collapse the months, select one of the quarters and press Alt + A H to collapse.
And now we have the quarter totals for each sales person.
If we want to expand to view the months again, we use Alt + A J.
Needless to say, if you had multi-year data, you could use the grouping dialog box to add years as yet another time period.
In this way, Excel allows you to track revenue from multiple time periods, all from one column of dates data.
When it comes to other types of numerical data, however, circumstances can require you to create new columns in the data set.
I’ll show you how to do this, and also how to add new data records to a pivot table, in the next lesson.