Ever faced a situation when you have asked to prepare a Weekly sales report?

Creating a Weekly report is super easy in Excel. There are several options in Excel that you can perform to get a Weekly Report:

Let’s look at these methods in detail below.

Watch it on YouTube and give it a thumbs-up!

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

Follow the step-by-step tutorial below and make sure to download the Excel workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

 

Using WEEKNUM formula

In the screenshot below, you have sales data for a company for which you need to create a weekly report.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 1: Create a new helper column that will provide the week numbers for the dates.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 2: Use the formula – WEEKNUM.  It will simply return the week number of a specified date. By default, Excel will consider that the week will begin from Sunday.

Type the formula: =WEEKNUM(E2)

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 3: This report contains multiple years. Week number should be combined with the year to provide accurate results.

To combine year and week num, use the formula: =G2&”-“&WEEKNUM(E2,1)

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 4: Copy-paste the formula below.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 5: Go to Insert > PivotTable.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 6: In the Create PivotTable dialog box, Click OK.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 7: Drag and drop Week Num in the Row field and Sales in the Values field.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

This will provide you with a Weekly Sales Report in Excel using the Weeknum formula.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

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

 

Using Group Selection

Last time we learned about Excel Group by Week 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 these examples below I show you how to Excel group by week starting from any day you choose.

 

EXAMPLE 1:

Follow the step-by-step tutorial below on How to group dates into weeks in Excel:

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 Excel group by week (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

This will create an Excel pivot table group by week!

 

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!

excel group by week

Let’s look at another example in which you can Excel group by week starting from a Sunday!

 

EXAMPLE 2:

In the example below, there are sales data including sales date, product name, and price sold.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

STEP 1: Go to Insert > PivotTable.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 2: In the Create PivotTable dialog box, select the table range and new worksheet option, and click OK.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 3: A PivotTable structure will be created in a new worksheet and you can use the PivotTable fields to create a report.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 4: Drag and drop Date in the Row field and Price in the Values field.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

This is how the PivotTable will look like:

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 5: Select any cell in the Date column and then Go to PivotTable Analyze > Group Selection.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 6: In the Grouping dialog box, select Days (deselect any other option highlighted) and type 7 in the number of days section.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

This will group the Pivot Table by a 7-days period.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

STEP 7: Type the first Sunday in the given range i.e. 28-06-2020 in this Starting at section.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

Your weekly sales report starting from a Sunday is now ready!

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

Similarly, you can also group dates by month, year, hour, second, etc.

 

Ungroup Data

Also, you can easily remove these groups created by following one of the two methods mentioned below:

  • Method 1: Right-click on the date column and click Ungroup.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

  • Method 2: Go to PivotTable Analyze and click on Ungroup.

Excel Group by Week Starting on a Monday With Excel Pivot Tables | MyExcelOnline

 

Conclusion

You can easily analyze data by week, month, year, days, hour, etc., and find trends using this either WEEKNUM formula or Excel group by week feature in Pivot Table.

These are fairly simple and super quick methods to group dates.

Did you know there are many creative ways of doing grouping in Excel Pivot Tables? Click here to learn all about it here!

 

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