In my previous Group By Month post, we grouped our Sales by each month.

However the cool thing with Excel is that we can take that a step further and customize our grouped date range!

In the example below I show you how to get the Sales Grouped by Custom Dates:




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, Years and make sure only Months are selected (which will be highlighted in blue). This will group our dates by the Months only.

We can now define a custom date range!

For example, we can only show the Sales numbers from January 2014 to September 2014.  To do this we need to set the following:

Starting at: 2014-01-01

Ending at: 2014-09-30

Click OK.



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



We can see that everything that does not fall in between January and September is Grouped into its own bucket.



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

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 based on your custom dates! Very 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

Sort an Item Row (Left to Right) With Excel Pivot ... I knew that I could sort virtually anywhere with Excel Pivot Tables, but I was surprised that I could even sort any row from left to right!!Below I have an Excel Pivot Table that consists of Sales Numbers over a three year period.Make sure to download the Excel Workbook b...
Automatically Refresh a Pivot Table Have you had challenges with constantly Refreshing a Pivot Table?People forget that each time your data source gets updated that you will also need to manually Refresh your Pivot Table in order for it to get updated and show the changes made.A lot of people ask if there i...
Show The Percent of Parent Row Total With Excel Pi... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Show The Percent of Parent Total With Excel Pivot ...  Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF PARENT TOTAL calculation.In the example below I show you how to get the Percent of a Product Total i.e The % Sales of each Product ...