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:

 

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-custom-date-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-custom-date-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-custom-date-03

 

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.

group-by-custom-date-04

 

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

group-by-custom-date-05

 

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

group-by-custom-date-06

 

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

group-by-custom-date-07

 

STEP 7: Select Number Format

group-by-custom-date-08

 

STEP 8: Select Currency. Click OK.

group-by-custom-date-09

You now have your total sales for each monthly period based on your custom dates! Very Easy!

group-by-custom-date-10

group-by-custom-date

HELPFUL RESOURCE:

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

Share on Google+

Related Posts

Clear & Delete Old Pivot Table Items Have you ever cleared, deleted or replaced your Pivot Table data/items but they still shows inside your Pivot Table filters?What gives??Well, you can easily clear your Pivot Table's old items from your Pivot Table's memory or cache.In our example below we have our Piv...
Group By Half Years With Excel Pivot Tables  In our previous post, we were able to Group by Month using Pivot Tables.  Let us take it up another notch, and group it by Half Years!In the example below I show you how to get the Sales Grouped by Half Years: January to June and July to December DOWNL...
Pivot Table Styles The default Pivot Table style in Excel is very bland and boring, stemming from a lack of creativity from the nerds over at Microsoft!You can give some life to your Excel Pivot Table by simply changing the Pivot Table Style!For our example, this is our Pivot Table:...
Show The Percent of Difference From Previous Years...  I am sure that your boss has asked you to come up with a Year on Year variance report at some stage.  There are a couple of ways to get him/her an answer.One is using Formulas, but that will take time to set up and you are exposed to errors!The other method is t...