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!
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 ***
In the example below, I show you how to Pivot Table Group by Month:
STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet
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:
STEP 3: Right-click on any row in your Pivot Table and select Group so we can select our Group order that we want:
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.
STEP 5: In the VALUES area put in the Sales field. This will get the total of the Sales for each Month & Year:
Now we have our sales numbers grouped by Month & Years, notice that we can improve the formatting:
STEP 6: Click the Sum of SALES and select Value Field Settings
STEP 7: Select Number Format
STEP 8: Select Currency. Click OK.
You now have your total sales for each monthly period! Quick & Easy!
This is how you can easily create Pivot Table Group Dates by Month!