How To Create A Pivot Table In Excel With Dates

How to Work with Excel PivotTable Dates

September 15, 2016
Categories: Excel®, PivotTables Tags: excel pivot table dates

With the most recent versions of Excel, you have gained more and more functionality with Pivot Tables. It is now much easier to work with dates. There are two features that you can put to work right away: data filtering (2007) and timeline slicer (2013). There's also a change to the way dates are displayed by default in an Excel 2016 PivotTable. We'll show you how to use it, how to turn it off and how to get similar functionality in earlier versions.

Download WorkingWithDatesExcelPivotTables.xlsx to see each of these solutions. You may also download WorkingWithDatesExcelPivotTables2010 which includes only 2010 compatible features.

Date Filtering (Excel 2007+)

With the introduction of Excel 2007, you were able to filter date data in a column by date range, for example, this quarter, last quarter, this week, last month, next month, between two dates. This made life a lot easier for those whose data must be analyzed in this way. Any date field you add to a row or column field (not value field) in a Pivot Table can take advantage of this feature. Just click the down arrow at the top of the column with the date. If you have several row fields, be sure you have a cell selected that shows date data. That will enable the date filters.

Unfortunately, this same functionality is not available if you add a date field to the Report Filter area. If you are using Excel 2013/2016, you can take advantage of the new Timeline Slicer.

Timeline Slicer

Slicers were introduced in Excel 2010 and could, more or less, take the place of the Report Filter (Filters) function in the layout. However, it did not give us any additional facility with dates. In Excel 2013, we can take advantage of the Timeline Slicer. To add a slicer:

Select a cell inside your Pivot Table.
On the Analyze tab, in the Filter group, click on the Insert Timeline button.
If you have more than one date field in your source data, choose the date field you want to use as your slider. In our example, there is only one, Date. Check the box, then OK.
The default measure will likely be Months. Click the down arrow to the right of the word Months and choose another measure, if you like.

Date Grouping in Excel 2016

Somewhat alarming, if you weren't expecting it is the way dates now appear as a row field in a Pivot Table in the newest version of Excel. By default you'll noticed dates are grouped by year, quarter and month, with + or – symbols to expand and collapse within these groups. Filter still work, but if you'd prefer to see just regular dates, right click on any date grouping and choose Ungroup.

How to get Month Names in 2007-2013

No matter what version you're using, to get a month name, rather than a date to show up as a selectable field in an Excel Pivot Table, add a column to your Source Data with this formula, where A2, refers to the dated cell. =TEXT(A2,"mmm").