Excel tip: Analysing data by date using PivotTables

One of the simplest changes in Excel 2016 relates to date data when you’re working in a PivotTable. In prior Excel versions date data was added to the PivotTable as is.

In this video there’s a set of data to analyse in a PivotTable – dates data relating to transactions.


Excel 2013

  • Navigate to the PivotTable Fields area > drag Date into Columns. You will see that all the dates are added as is into the column headings.
  • Right click the dates and select Group > select Months, Quarters, Years > Ok. The data will now be analysed and produced in a more meaningful way.

Excel 2016

  • Navigate to the PivotTable Fields area > drag Date into Columns. You will see that it automatically groups the data – dates are usually grouped by months, quarters and years.
  • If you do not want your dates to be grouped in Excel 2016 > navigate to the PivotTable Fields area > remove Years and Quarters > right click on month > select Ungroup and you’re back to how you were.


A simple but effective change in Excel 2016 if you work with date data.


Looking to learn more about Excel? See more free Microsoft tips or browse our range Microsoft Excel courses.