The ability to group date fields automatically is one of the most powerful and useful features of pivot tables. With a few clicks, you can neatly roll up thousands of dates into various units of time, including years, quarters, months, weeks and days.
Let’s take a look.
This pivot table shows total sales by product. Let’s add the date field as a column, then group the date to show sales by year.
When you first add a date field as a row or column label, Excel will break out the values by every value in the field. To group a date field, first select one of the dates. Then right-click and choose Group from the menu.
Excel will display a grouping dialog box, with a list of date and time units. In this case, we just need to select years. When we click OK, Excel groups Total Sales by Year.
When you group by date you can group by more then one unit at a time. For example, let’s add Quarters to our pivot table. First, select one of the Years. Then, right-click and choose Group. Now just click Quarters to add them to Years. When we click OK, we get a breakdown of sales by year and quarter.
In the same way, we can easily remove Quarters and add Months.
Note that when a date field is grouped, the units of grouping are visible as separate fields. This means we can move the fields to different areas. For example, we can move Years to the row labels area.
When you’re working with data that spans several years, be aware that it’s possible to group dates by month or quarter without including years. For example, if we remove years from our grouping, we get total sales by month, but each month includes data that spans more than one year. This can be misleading and cause confusion, so be careful with the settings.
When grouping dates, be aware that all of your field data has to contain a valid date. If even one record is blank or is otherwise an invalid date, Excel will not let you group the field. To illustrate, if we delete the date from the first record in the source data, then refresh the pivot table, the grouping is broken. And if we try to re-group the field, we receive an error message.
Grouping by dates is very flexible. We can easily rearrange this pivot table into a variety of useful displays.