Pinterest Pixel

Group By Custom Dates With Excel Pivot Tables

Bryan
In my previous Group By Month post, we grouped our Sales by each month.
Group By Custom Dates With Excel Pivot Tables | MyExcelOnline Group By Custom Dates With Excel Pivot Tables | MyExcelOnline

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:

 

Group By Custom Dates With Excel Pivot Tables | MyExcelOnline

download excel workbook Group-by-Custom-Date.xlsx

 

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 Dates With Excel Pivot Tables

 

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 Dates With Excel Pivot Tables

 

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 Dates With Excel Pivot Tables

 

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 Dates With Excel Pivot Tables

 

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 Dates With Excel Pivot Tables

 

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

Group By Custom Dates With Excel Pivot Tables

 

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 Dates With Excel Pivot Tables

 

STEP 7: Select Number Format

Group By Custom Dates With Excel Pivot Tables

 

STEP 8: Select Currency. Click OK.

Group By Custom Dates With Excel Pivot Tables

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

Group By Custom Dates With Excel Pivot Tables

 

Group By Custom Dates With Excel Pivot Tables | MyExcelOnline

If you like this Excel tip, please share it
Group By Custom Dates With Excel Pivot Tables | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  2 Quick Ways to Group Time in Excel Pivot Tables

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...