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.
Creating Groups (00:04)
Dates can cause problems in Pivot Tables as the date values don’t repeat themselves over time. When you add a date field to a Pivot Table, you can get a separate row or column for each date. Excel 2016 is capable of grouping the date field automatically, but it might not create the group you want.
In our example, we want to group dates by month or quarter to help analyze the performance of our salespeople. We do this by selecting a date column and pressing Right Mouse Button Key, G. This opens the grouping dialog box, where we select the fields we want to group by, and the time period to include. For example, grouping by months will show us total revenue figures for each month.
Grouping Multiple Fields (01:16)
We can group by more than one field. For example, selecting Months and Quarters in the Grouping dialog box groups sales by quarters, and then by months within those quarters. With this view, we can collapse the months and view quarterly totals if we want. We do this by selecting a quarter and hiding the months with Alt, A, H. We can later unhide the months with Alt, A, J.
In the previous lesson, we learned how to change the output values displayed in a pivot table. In this lesson, we'll learn how to group date columns to make them easier to analyze.
Analyzing dates in a pivot table can prove problematic, because the column entries don't repeat themselves over time.
Let's say we add the payment date field to the columns label, and remove the state field.
This gives every individual data separate column. And as a result, we can't effectively compare salesperson 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 built-in solution, so that we don't have to create new data columns. Instead, we'll return to the pivot table and select any of the dates.
We'll then press the right mouse button key and G for group.
This brings up the grouping dialog box, and allows us to select a time period. We'll pick months, and press okay.
This command groups all the date columns by month, and returns the total revenue for each month on our pivot table. What's more, we can select two time periods at one time.
Let's return to the grouping dialog box.
And this time, we'll select quarters as well as months.
And now we 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 AH to collapse.
Now we have the quarter totals for each salesperson. If we want to expand to view the months again, we use alt alt AJ.
Needless to say, if we had multi-year data, we could use the grouping dialog box to add years as yet another time period.
In this way, Excel allows us 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 us to create new columns in the data set.
In the next lesson, we'll learn how to do this, and also how to add new data records to a pivot table.