Microsoft

Pro tip: Group an Excel PivotTable by dates

Grouping lots of data by dates is easy using Excel's PivotTable. Susan Harkins shows you how.

A PivotTable is a great way to summarize a lot of data. The technical side of things is simple — you insert a PivotTable and add the appropriate fields. If you're grouping by dates, you'll have lots of options, but all of the possibilities aren't as obvious as you might like. In this article, I'll show you how to group a PivotTable by date components. I'll also show you a few of the less-than-obvious possibilities.

You can work with almost any dated data or download the example 2010 .xlsx, 2013 .xlsx, or .xls workbook. I'm using Excel 2010 but noting significant differences between 2003 and 2013.

Create the PivotTable

The PivotTable and data shown in Figure A contain only a few rows to keep things simple. By default, the PivotTable gives you a good start toward grouping your data without much effort on your part:

  • The PivotTable sorts the dates in a meaningful way
  • The PivotTable sums sold values for the same date and region (sold values that don't share a region aren't summed)
  • The PivotTable displays a grand total for each date and region

Figure A

Figure A
Our example data and initial PivotTable.

To create the example PivotTable, do the following:

  1. Click any cell in the data set.
  2. Click the Insert tab.
  3. Click PivotTable in the Tables group and click OK (don't change any of the default settings).
  4. In the resulting field list, drag the Date field to the Row Labels list, drag Region to Column Labels, and drag Sold to Values (Figure B). By default, the PivotTable will total the Sold values, but you can change the function.

Figure B

Figure B
The PivotTable will total the Sold values.

In Excel 2003, choose PivotTable and PivotChart Report from the Data menu to launch a wizard that will walk you through the process of creating the PivotTable. Then, use the PivotTable Field List to add the fields to the frame as noted in step 4 (Excel 2003 uses the term Data Area instead of Values).

Group by date components

Initially, there's a bit of grouping going on already. Specifically, the PivotTable summarizes by date, which is what you might expect. Now, let's look at a few easy sorts. To get started, right-click the date column in the PivotTable. Then, choose Group from the resulting sub-menu to display the dialog shown in Figure C (you can also sort by time values, but we won't explore those options in this article).

Figure C

Figure C
Because you're grouping dates, Excel offers a number of types of date groups.

First, click the selected option to unselect it (you can select multiple options). Then, select Days and click OK to get the results shown in Figure D. As you can see, this group ignores the year and combines both April 19 dates. This may or may not be what you want.

Figure D

Figure D
Sort by days.

If you want to sort by days and the year, select both Days and Years in the dialog as shown in Figure E. You can even add Months to the mix if you want to expand and collapse more details in the resulting table.

Figure E

Figure E
Sort by days and years.

To group by months, as shown in Figure F, choose Month from the dialog. This configuration also ignores the day and year.

Figure F

Figure F
Sort by months.

Similarly to the last example, group by Month and Year to consider the year, as shown in Figure G.

Figure G

Figure G
Add years to the month sort.

It's worth noting that when you add a second (third or more) group using the dialog, Excel adds that group to the PivotTable's Field List (see Figure B). You can then manipulate that field further, but doing so often has undesirable results. In the right circumstances and used appropriate, this flexibility can add amazing insight. For instance, you can add it to the Report Filter list to create a simple and effective filter. Figure H shows only the records for 2013 after moving Year to the Report Filter and using it.

Figure H

Figure H
Let users filter the PivotTable by date components.

This behavior is helpful, but it can also achieve mixed results. If your PivotTable isn't grouping as you expected, check the Field List and delete any leftover date component fields that might be interfering with the current group.

To continue with a few more simple examples, let's group our example data by Quarter. Again, Excel won't distinguish by years; add Year to return the results shown in Figure I.

Figure I

Figure I
Group by yearly quarters.

We've looked at several simple date sorts so far, but you might have noticed that there's no week component in the dialog list. This group is one of those less-than-obvious options that I mentioned earlier. To group by weeks, use the Day option and change the Number of days option from 1 to 7, as shown in Figure J.

Figure J

Figure J
Group by weeks.

You might be wondering about the Starting at and Ending at options. By setting these dates, you can filter the records that make it to the PivotTable. This is a helpful feature when you're dealing with lots of data. You can quickly determine your first group by setting these two dates. We'll look at this option in the next section.

About those weeks

At first glance, you might think the weekly groups (Figure J) are fine — and they might be, but let's take a closer look. If you open the dialog, you'll notice that the Starting at date is also the first date in your data set, March 14, 2011. That makes sense, but this setting controls more than you might realize. Specifically, March 14, 2011 is a Monday. Therefore, Excel will use Monday as the first day of the week.

To change the first day of the week, change the Starting at date, accordingly. For example, let's suppose you want your week to begin on Sunday. To force this change, change the Start at date to the nearest Sunday that occurs before your data set's first date. In this case, you'd change the Starting at date to March 13, 2011 (a Sunday) as shown in Figure K (when Excel unchecks the Starting at option, leave it unchecked). As you can see, the PivotTable adjusts nicely!

Figure K

Figure K
Set the first day of the week.

It isn't enough to choose any Monday. Be sure to choose the Monday that most closely accommodates the date range you want to allow. By setting the wrong Starting at date, you could inadvertently exclude (or include) records you don't intend.

Only for Excel 2013 users

Excel 2013 has a new filtering feature called timelines. They're similar to slicers, but they're specific to dates. To create a timeline for the example PivotTable, do the following:

  1. Select a cell in the PivotTable.
  2. Click the contextual Analyze tab.
  3. Click Insert Timeline in the Filter group.
  4. In the resulting dialog, click the Date field and click OK.

The new timeline, shown in Figure L lets you group by months, but you can change this to years, quarters, or even days by clicking the Months drop-down and making the appropriate selection.

Figure L

Figure L
Use the timeline to display specific time periods.

For example, to display records for 2012, as shown in Figure M, change the date component from the default of Months to Years and then click 2012 in the updated timeline bar.

Figure M

Figure M
Filter the records using the timeline.

To learn more about Excel's new timeline feature, you can read "10 steps to adding a timeline to an Excel 2013 PivotTable."

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox