Before I was a Pivot Table guru, I had to get individual rows of daily sales and group them into a report showing the monthly sales during the year.

Group Dates in Pivot Table would take a ton of effort using Formulas:

  • Extracting the month and year from each transactional date;
  • Then manually grouping them together to get the total sales numbers for each month.  PAINFUL & SLOW!

Thankfully there is the Pivot Table way (I wish I had known this back then), which is quick and reduces the risks of making any errors….ah yeah & I almost forgot, it is also easy to add new data to your sales report with a simple Refresh!

 

In this article, we will be covering the following topics in detail:

Let’s look at each one of these!

 

Group Dates in Pivot Table by Month & Year

In the data below, you can see that there are two columns: one that contains the transaction date of the sale, and the second column contains the total sales amount for a particular date.

sales data

Want to know How To Group Dates in Pivot Table by Month?

*** Watch our video and step by step guide below on Pivot Table date grouping by month with free downloadable Excel workbook to practice ***

Watch on YouTube and give it a thumbs up 👍

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

In the example below, I show you how to Pivot Table Group by Month:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

group-by-month-01

 

STEP 2: In the ROWS section put in the Order Date field.

Notice that in Excel 2016 (the version that I am using) it will automatically Group the Order Date into Years & Quarters:

group-by-month-02

 

STEP 3: Right-click on any row in your Pivot Table and select Group so we can select our Group order that we want:

group-by-month-03

 

STEP 4: We need to deselect Quarters and make sure only Months and Years are selected (which will be highlighted in blue).

This will group our dates by the Months and Years.  Click OK.

group-by-month-04

 

STEP 5: In the VALUES area put in the Sales field.  This will get the total of the Sales for each Month & Year:

group-by-month-05

 

This is how you can easily create Pivot Table Group Dates by Month!

group-by-month-06

 

Group Dates in Pivot Table by Week

To group the dates by week, follow the steps below:

STEP 1: Right-click on one of the dates and select Group.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 2: Select the day option from the list and deselect other options.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 3: In the Number of days section, type 7.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

This is how the group dates in Pivot Table by week will be displayed.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 4: You can even change the starting date to 01-01-2012 in the section below.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

Your final grouped data is ready!

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

Change Formatting

Now we have our sales numbers grouped by Month & Years, notice that we can improve the formatting by following the steps below:

group-by-month-06

 

STEP 1:Click the Sum of SALES and select Value Field Settings

group-by-month-07

 

STEP 2: Select Number Format

group-by-month-08

 

STEP 3: Select Currency. Click OK.

group-by-month-09

 

You now have your total sales for each monthly period!  Quick & Easy!

Group Dates in Pivot Table

 

Summarize Value by

In the previous examples, you saw how to get total sales by month, year, or week. You can even calculate the total number of sales that occurred in a particular month, year, or week.

Let’s look at an example to know how:

STEP 1: Right-click anywhere on the Pivot Table.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 2: Select Value Field Settings from the list.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 3: In the Value Field Setting dialog box, select Count.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 4: Click OK.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

This will summarize the values as a count of sales instead of the sum of sales (like before).

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

Ungroup Dates

To ungroup dates in a Pivot Table, simply right-click on the dates column and select ungroup.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

Or, you can go to the PivotTable Analyze tab and select Ungroup.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

Once this is done, the data will be ungrouped again.

sales data

 

Control Automatic Grouping

If you wish to, you can easily turn off this automatic date grouping feature in Excel 2016. To do that, follow the steps below:

STEP 1: Go to File Tab > Options

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 2: In the Excel Options dialog box, click Data in the categories on the left.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 3: Check Disable automatic grouping of Date/Time columns in PivotTables checkbox.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

 

STEP 4: Click OK.

Group By Month, Year & Week With Excel Pivot Tables | MyExcelOnline

This will easily turn off the automatic grouping feature in the Pivot Table! So, the date will be not be grouped automatically now when you drag the date field to an area in the pivot table.

 

Conclusion

You can easily analyze data by week, month, year, days, hour, etc., and find trends using this grouping dates feature in Pivot Table. It is a fairly simple and super quick method to group dates.

Did you know there are many creative ways of doing grouping in Excel Pivot Tables?

Learn all about it here!

 

HELPFUL RESOURCE:

PIVOT BANNER

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn