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.

Grouping these 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 the example below I show you how to get the Sales Grouped 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 taht 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!





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


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Lock the Excel Pivot Table but not the Slicer Sometimes when you are sharing an Excel Pivot Table with your colleagues you do not want the other user(s) to mess with your Pivot Table layout and format.What you can do is lock the Pivot Table and only allow the user(s) to select the Slicers, making your report interact...
Pivot Table Count Transactions The default Value Field Setting when analyzing data with a Pivot Table is to Sum, but you can also analyze a Pivot Table with a Count.This is useful when you want to see how many sales transactions  took place within a region, a month, a year or per business unit.DOWNLOAD...
Show The Difference From Previous Years With Excel... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation.You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you ...
Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation.You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...