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:

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 taht 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

 

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

group-by-month-06

 

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

group-by-month-07

 

STEP 7: Select Number Format

group-by-month-08

 

STEP 8: Select Currency. Click OK.

group-by-month-09

 

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

group-by-month-10

group-by-month

HELPFUL RESOURCE:

PIVOT BANNER

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Drill Down To Audit Using a Pivot Table When you are using a Pivot Table in Excel and want to know what data makes up a certain value, all you have to do is double click on that cell. This will open up a brand new Sheet with all the rows of data that make up that value. NB. This is an extraction of your data sour...
Group Dates with a Pivot Table Grouping Dates is very easy with a Pivot Table.  All you have to do is Right Click on your Date values (which are either in the Row or Column Labels of your Pivot Table), then choose the Group option.  From the dialogue box you can choose to Group by Days, Months, Quarters or Yea...
Filter a Pivot Table by Dates There are an array of different Date filters in a Pivot Table.  You can filter by a particular date range, for example: by this week, next month, next quarter, next year, last year, year to date and the list goes on and on.  This is useful if you want to see what invoices are due...
Refresh a Pivot Table When the information in your data set gets updated you need to Refresh your Pivot Table to see those changes in your Pivot Table.  There are three ways to do this.  First click on your Pivot Table and: 1. From the Ribbon choose: PivotTable Tools > Options > Refresh 2....