Last time we learned about Grouping Sales by Weeks using Pivot Tables. But what if your boss wanted all of the weeks to start on a Monday? You would be scrambling to find a way to ensure all weeks start on a Monday!

But here’s the thing, setting it to start on Mondays is very easy!

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

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-monday-01

 

STEP 2: In the ROWS section put in the Order Date field.

Notice that in Excel 2016 it automatically groups dates into Years & Quarters.

group-monday-02

 

STEP 3: Right click on any entry in your Pivot Table Row Labels and select Group

group-monday-03

 

STEP 4: Notice that it was able to determine our minimum and maximum dates. However we need our weeks to start on a Monday.

group-monday-04

Our starting date is January 3, 2012.  Let us have a quick check in our calendar:

group-monday-05

We can see that the nearest Monday before that date, is January 2, 2012.

Deselect Months, Quarters and Years and make sure only Days is selected (depicted by the blue color).

Set the Number of days to 7. 

Set the Start Date as 2012-01-02.

This will group our dates by a weekly range (every 7 days starting on a Monday).

Click OK.

group-monday-06

 

STEP 5: In the VALUES area put in the Sales field.

This will get the total of the Sales for each 7-day date range you have defined:

group-monday-07

 

STEP 6: We can now format our Sales numbers into something more presentable.

group-monday-08

Click the Sum of SALES and select Value Field Settings.

group-monday-09

 

STEP 7: Select Number Format

group-monday-10

 

STEP 8: Select Currency. Click OK.

group-monday-11

You now have your total sales for each 7-day period starting on Mondays!

group-monday-12

group-monday

HELPFUL RESOURCE:

PIVOT BANNER

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+