How to group a pivot table by date

Lets take a look.

This pivot table shows total sales by Product.

Lets add the Date field as a column label, and then group to show sales by year.

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 might group by more than one unit at a time.

For example, lets 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.

This can be misleading and cause confusion, so be careful with grouping dates.