Abstract
Transcript
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 label, and then group to show sales by year.
When you first add a Date field as a row or column label, Excel will break out the data for 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 than 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.
Notice 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 grouping dates.
