{"id":5598,"date":"2020-12-03T20:29:22","date_gmt":"2020-12-03T19:29:22","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=5598"},"modified":"2024-03-22T18:54:47","modified_gmt":"2024-03-22T17:54:47","slug":"group-sales-weeks-starting-monday-excel-pivot-tables","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/group-sales-weeks-starting-monday-excel-pivot-tables\/","title":{"rendered":"Excel Group by Week Starting on a Monday With Excel Pivot Tables"},"content":{"rendered":"

Ever faced a situation when you have asked to prepare a Weekly sales report<\/a><\/strong>?<\/p>\n

Creating a Weekly report is super easy<\/strong> in Excel. There are several options<\/strong> in Excel that you can perform to get a Weekly Report:<\/p>\n

Let’s look at these methods in detail below.<\/p>\n

Follow the\u00a0step-by-step tutorial<\/strong>\u00a0below and make sure to download the Excel workbook to follow along:<\/p>\n

download excel workbook<\/strong><\/span>Group-Monday-1.xlsx<\/span><\/a><\/div>\n

 <\/p>\n

<\/a>Using WEEKNUM formula<\/span><\/h3>\n

In the screenshot below, you have sales data<\/strong> for a company for which you need to create a weekly report.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 1:<\/strong> <\/span>Create a new helper column<\/strong> that will provide the week numbers for the dates.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong>\u00a0<\/span>Use the formula – WEEKNUM<\/strong><\/a>. \u00a0It will simply return the week number of a specified date. By default<\/strong>, Excel will consider that the week will begin from Sunday<\/strong>.<\/p>\n

Type the formula<\/a>: =WEEKNUM(E2)<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong>\u00a0<\/span>This report contains multiple years<\/strong>. Week number should be combined with the year to provide accurate results.<\/p>\n

To combine year and week num<\/strong>, use the formula: =G2&”-“&WEEKNUM(E2,1)<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong>\u00a0<\/span>Copy-paste the formula below<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 5:<\/strong>\u00a0<\/span>Go to Insert<\/strong> > PivotTable<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 6: <\/strong>In the Create PivotTable<\/a> dialog box, Click OK.<\/span><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 7: <\/strong><\/span>Drag and drop Week Num in the Row field<\/strong> and Sales in the Values field<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

This will provide you with a Weekly Sales Report in Excel using the Weeknum formula.<\/p>\n

\"Excel<\/a><\/p>\n

You can also use the group selection feature in Pivot Tables<\/strong> to Excel group by week. Let’s dive into this method<\/strong> and see how it works.<\/p>\n

 <\/p>\n

<\/a>Using Group Selection<\/span><\/h3>\n

Last time we learned about Excel Group by Week<\/a><\/strong>\u00a0using Pivot Tables. But what if your boss wanted all of the weeks to start on a Monday<\/strong>?<\/p>\n

You would be scrambling to find a way to ensure all weeks start on a Monday!<\/p>\n

But here’s the thing, setting it to start on Mondays is very easy!<\/p>\n

In these examples below I show you how to Excel group by week starting from any day you choose.<\/h4>\n

 <\/p>\n

EXAMPLE 1:<\/strong><\/span><\/p>\n

Follow the step-by-step tutorial<\/strong> below on How to group dates into weeks in Excel:<\/p>\n

STEP 1:<\/strong><\/span>\u00a0<\/span>Insert a new Pivot table by clicking\u00a0on your data and going\u00a0to\u00a0Insert > Pivot Table > New Worksheet or Existing Worksheet<\/em><\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 2:<\/strong><\/span>\u00a0In the\u00a0ROWS<\/strong>\u00a0section put in the\u00a0Order Date<\/strong>\u00a0<\/em>field.<\/p>\n

Notice that in Excel 2016 it automatically groups dates into Years\u00a0& Quarters.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 3:<\/strong><\/span>\u00a0<\/span>Right-click on any entry\u00a0<\/em>in your Pivot Table Row<\/a> Labels and select\u00a0Group<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 4:<\/strong><\/span>\u00a0<\/strong>Notice that it was able to determine our minimum and maximum dates. However, we need our weeks to start on a Monday<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

Our starting date is January 3, 2012<\/strong>. \u00a0Let us have a quick check-in our calendar:<\/p>\n

\"Excel<\/a><\/p>\n

We can see that the nearest Monday before that date, is January 2, 2012<\/strong>.<\/p>\n

Deselect\u00a0Months, Quarters, <\/strong>and\u00a0Years<\/strong> and make sure only\u00a0Days\u00a0<\/strong>is selected (depicted by the blue color).<\/p>\n

Set the Number of days\u00a0<\/strong>to\u00a07.\u00a0<\/strong><\/p>\n

Set the\u00a0Start Date\u00a0<\/strong>as 2012-01-02.<\/p>\n

This will Excel group by week (every 7 days starting on a Monday<\/strong>).<\/p>\n

Click\u00a0OK.<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 5:\u00a0<\/strong><\/span>In the\u00a0VALUES <\/strong>area\u00a0put in the\u00a0Sales\u00a0<\/em><\/strong>field.<\/p>\n

This will get the total of the Sales for each 7-day date range you have defined:<\/p>\n

\"Excel<\/p>\n

This will create an Excel pivot table group by week!<\/p>\n

 <\/p>\n

STEP 6:\u00a0<\/strong><\/span>We can now format our Sales numbers<\/a><\/strong> into something more presentable.<\/p>\n

\"Excel<\/a><\/p>\n

Click the Sum of SALES<\/strong> and select\u00a0Value Field Settings<\/a>.<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 7:\u00a0<\/strong><\/span>Select\u00a0Number Format<\/a><\/strong><\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 8:\u00a0<\/strong><\/span>Select\u00a0Currency.\u00a0<\/strong>Click\u00a0OK.<\/strong><\/p>\n

\"Excel<\/a><\/p>\n

You\u00a0now have your total sales for each 7-day period starting on Mondays<\/strong>!<\/p>\n

\"Excel<\/p>\n

Let’s look at another example in which you can Excel group by week starting from a Sunday!<\/p>\n

 <\/p>\n

EXAMPLE 2:<\/strong><\/span><\/p>\n

In the example below, there are sales data<\/strong> including sales date, product name, and price sold.<\/p>\n

\"Excel<\/a><\/p>\n

 <\/p>\n

STEP 1: <\/strong><\/span>Go to Insert > PivotTable.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 2: <\/strong><\/span>In the Create PivotTable dialog box, select the table range<\/strong> and new worksheet option<\/strong>, and click OK<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 3: <\/strong><\/span>A PivotTable structure will be created<\/strong> in a new worksheet and you can use the PivotTable fields to create a report.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 4: <\/strong><\/span>Drag and drop Date in the Row field<\/strong> and Price in the Values field<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

This is how the PivotTable will look like:<\/p>\n

\"Excel<\/a><\/p>\n

STEP 5: <\/strong><\/span>Select any cell in the Date column and then Go to PivotTable Analyze<\/strong> > Group Selection<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 6: <\/strong><\/span>In the Grouping dialog box, select Days<\/strong> (deselect any other option highlighted) and type 7 in the number of days section<\/strong>.<\/p>\n

\"Excel<\/a><\/p>\n

This will group the Pivot Table by a 7-days period<\/a>.<\/p>\n

\"Excel<\/a><\/p>\n

STEP 7: <\/strong><\/span>Type the first Sunday in the given range i.e. 28-06-2020<\/strong> in this Starting at<\/strong> section.<\/p>\n

\"Excel<\/a><\/p>\n

Your weekly sales report starting from a Sunday<\/strong> is now ready!<\/p>\n

\"Excel<\/a><\/p>\n

Similarly, you can also group dates<\/strong><\/a> by month, year, hour, second, etc.<\/p>\n

 <\/p>\n

Ungroup Data<\/strong><\/span><\/p>\n

Also, you can easily remove these groups created by following one of the two methods mentioned below:<\/p>\n