Grouping Dates in PivotTables

Excel, Microsoft Office, PivotTables | Comments Off on Grouping Dates in PivotTables

 I have a client that is tracking sales. We have over 100 individual records, and the data source is going to grow through out the year. With multiple sales each day, we’ll want to be able to sum total sales at each week. PivotTables are the obvious answer.

We need to group by calendar week, Mon-Sun.

First step after creating the Pivot Table was to Group the date field, based on 7 days:

Microsoft Office PivotTable training

   Which resulted in this:

Microsoft Office PivotTable training

However, by default Excel is using the first date in the data source as the first day of the week (1/2/2012 was a Thursday, so the 7 day week is Thu – Wed) rather than Mon – Sun.

To fix this, we needed to edit the group settings so the start date was actually the Monday prior to the first record in our data source (12/30/2013):

Microsoft Office PivotTable training

(also, notice that the Auto Starting at and Ending at check boxes are unchecked. I entered 12/31/2014 as the end date while I was at it, since the data source will ultimately extend to the end of the year.)

 and have the Pivot Table group look like this:

Microsoft Office PivotTable training

 And that fixed that.

Learn more about absolute vs relative references  and using proper Order of Operations. Or contact us and ask about personalized training in Excel.