Search form

Grouping by Date in an Excel Pivot Table

This lesson shows you how to group data in your pivot table if you have included a date field in the pivot table. This lesson picks up on the work we did in our first lesson on creating a Pivot Table, which introduced Pivot Tables and showed you how to create a basic Pivot Table from a table of source data. In this lesson we will extend that pivot table by grouping our data by date into weeks, months, quarters or years.

In that first lesson, we took a data table that looked like this (note how many rows there are in the table - this table covers a three month period for four sales people):

Excel Pivot Table example, source data table

We then turned that data into a Pivot Table that looked like this:

Excel Pivot Table created from an example table of source data

As you can see, the pivot table above has been designed to show product sales broken down first by day and then by sales person. Now, let's look at what else we can do with this pivot table.

We'll start by reviewing how to access the Pivot Table Tools toolbar. This toolbar isn't essential to complete this lesson, but it helps to know how to find it when you need it.

Accessing the Pivot Table Tools toolbar

To access the Pivot Table Tools toolbar, follow these steps:

  • Click on a cell anywhere inside your pivot table.
  • Check the ribbon toolbar for this option:
    Excel Pivot Table, how to access the Pivot Table Tools toolbar
  • Then, click either Options or Design to see the related toolbar. The work we'll do in this lesson will focus exclusively on the Options toolbar.

Grouping your data by Date

  • To group your data by Date, make sure you have a valid Date field in your pivot table.
    • The Date field in your source data should only include valid dates. If it doesn't, you may not be able to group your data.
    • The Date field can be either a Row or a Column in your Pivot Table, although you may find that placing it in a Row is the most practical option if your source table includes multiple records over a long period of time, since your pivot table will then spread across many columns.
    • You need at least on additional field after the Date field in your pivot table. In our example, our Row fields look like the following. When we group by Date, we will see data for each Salesperson aggregated into the date period that we chose:
      Excel Pivot Table layout showing two fields in the Row Labels section
  • Once you are ready to group your data, select a date field from within the pivot table and click either the Group Selection button or the Group Field button on the toolbar (both will work in this scenario):
    Excel Pivot Table Group and Ungroup buttons
  • Alternatively, simply right-click on one of the dates in the first column, and select the Group option as shown here:
    Excel creating a Pivot Table, grouping data in a pivot table by date
  • Note that if you see a message like this, then either you didn't click the correct date field or some of your dates are not valid date. In the latter case you'll need to review your source data and fix it. Don't bother about clicking the "Was this information helpful?" link unless you want to give Microsoft some feedback on this message.
    Excel Pivot Table error when grouping by a field that can't be grouped.
  • If you have attempted to group on a valid field type (date in our example), then you will see the following options dialog box where you can choose how you want to group the data in your pivot table:
    Excel Pivot Table options for grouping data by date
  • Excel will automatically select the start and end dates for the grouping based on the dates in your data. You can change these dates at this point so that the grouping only covers a given date range. In our case, we'll leave the dates unchanged.
  • You can then choose what you want to group your data by. Note that the Months option has been chosen by default.
  • In our scenario, we want to group by Weeks, rather than by Months. As you can see, there is no option to choose weeks. However, we can still get the data to group by weeks.
    • First, click Months to deselect it in the list (Excel will let you group by more than one option, but that's more than we need right now).
    • Then, click Days.
    • Next, enter 7 as the Number of Days value.
    • Finally, click OK.
  • Your pivot table should now look something like this:
    Excel pivot table example with data grouped by week
  • As you can see in this example, the data has been grouped in 7 day blocks starting from the first date in the data range. If this date was the wrong date on which to start the weekly grouping, (e.g. 2/01/2012 was Wednesday and you wanted the groupings to start on Mondays) then you could repeat the steps above to get to the Grouping options dialog box and change the starting date
  • Note that you can remove the grouping at any times by following the steps above and choosing Ungroup instead of Group. The groupings will be removed immediately.

Expanding and Collapsing Data Groupings

Once your data has been grouped, you can then collapse or expand individual group fields to hide the underlying data (in our example, grouping will hide the individual sales people).

  • Select the field you want to collapse, and clicking the Excel pivot table icon for collapsing a group of data button to the left of the field heading.
  • This screenshot shows the first two weeks collapsed:
    Excel Pivot Table with grouping by date and the first two groups collapsed to hide the underlying detail
  • You can also collapse or expand all of the fields at once.
    • Click on one of the fields, and then click either the Expand or Collapse buttons on the Pivot Table toolbar (remember that you need to click on a cell inside the pivot table and then click the Pivot Table Tools button above the main ribbon toolbar).
    • Note that in the example below, the Salesperson field is active. However, it doesn't matter at this point whether the Salesperson or Date field is active - the buttons will do the same thing either way because the Salesperson field is inside the Date grouping.
      Excel Pivot Table ribbon toolbar buttons for expanding or collapsing an enter field.
    • If you want more (or faster) control over how you expand or collapse your grouped data, you can also right-click one of the Date fields and choose Expand/Collapse to see different options for expanding or collapsing the fields in the group.
      Excel Pivot Table choosing options to expand or collapse individual fields or all fields in a group
  • Let's look at the Expand and Collapse options in more detail:
    • Expand and Collapse will expand or collapse the field you selected. If the group is already expanded or collapsed, choosing those options won't do anything.
    • Expand Entire Field and Collapse Entire Field will either expand or collapse all of the data in the group, i.e. all weeks will be expanded or collapsed.
    • In our example, Collapse to "Date" and Expand to "Salesperson" will have the same effect as Expand and Collapse. This option becomes more useful if you have used more than one grouping in your pivot table.

Summary

This lesson covered two key tasks with Excel Pivot Tables:

  • Grouping data based on dates to show your data in aggregated groups (i.e. weekly rather than daily sales data).
  • Expanding and collapsing groups to show or hide detail within your pivot table.

As always with subjects as complicated as Pivot Tables, there are more things you can do, and more options to explore. If you've found this lesson useful but want to know more, why not ask a question the Comments section below. You can also let us know if there is anything additional you think we should cover.

Our Comment Policy.

We welcome your comments and questions about this lesson. We don't welcome spam. Our readers get a lot of value out of the comments and answers on our lessons and spam hurts that experience. Our spam filter is pretty good at stopping bots from posting spam, and our admins are quick to delete spam that does get through. We know that bots don't read messages like this, but there are people out there who manually post spam. I repeat - we delete all spam, and if we see repeated posts from a given IP address, we'll block the IP address. So don't waste your time, or ours. One other point to note - if you post a link in your comment, it will automatically be deleted.

Add a comment to this lesson

Comments on this lesson

Thanks!

Excellent page! :-)

Filter and group in the same time

Nice article !

In my pivot table I have only one date field. I would like to filter this date by YEAR (preferable showing Year filter at the top), and group this date by MONTH (say as columns). But it seems when I move Date field from Rows to Filter Area, it disappeared from Rows Area, and I can filter, but not see results by Month (and vice versa). Is it possible to have very same Date column both in the filter area and in the rows area, in the same time ?

You can't re-use fields in Pivot tables, with one exception

Hi Dean

Sorry, you can't re-use pivot table fields in the way you've asked.

The only time you can use fields more than once in a pivot table is in the Values area. For example, you might want to show the number of sales (COUNT) and the total of sales (SUM) side by side in the pivot table.

A workaround for your scenario could be to create a new column next to the Date column in your original data and place a formula in that column which copies the value from the original date column across.

In other words, if your date column is column C with the duplicate date column in column D, then column D would have formulas like =C2, =C3, =C4 etc. That way the second date column is always the same as column C, and you can then use it as a second date field.

Regards

David

Query of a PIVOT table

Hi Dean,

It was an excellent page but i got stuck at one point while grouping the excel sheet showed Cannot Group That section so hw to proceed further

pivot tables

sir how to create the start date and end date in piovt table field ?

Add comment