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:
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 it automatically groups dates into Years & Quarters.
STEP 3: Right click on any entry in your Pivot Table Row Labels and select Group
STEP 4: Notice that it was able to determine our minimum and maximum dates. However we need our weeks to start on a Monday.
Our starting date is January 3, 2012. Let us have a quick check in our calendar:
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).
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:
STEP 6: We can now format our Sales numbers into something more presentable.
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 7-day period starting on Mondays!