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

Key Takeaways:

• The ability to group data by week starting on a Monday in Excel pivot tables can be achieved through a series of straightforward steps.
• Firstly, insert your pivot table from your dataset, then place the date field within the ROWS section. Right-click a date value and select ‘Group’, adjusting the start date to a Monday and setting the number of days to 7.
• This custom start day can help align reporting with business weeks that traditionally start on Mondays, ensuring consistent weekly data comparisons.

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

Using WEEKNUM formula

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

 

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

 

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)

 

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)

 

STEP 4: Copy-paste the formula below.

 

STEP 5: Go to Insert > PivotTable.

 

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

 

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

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

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

 

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 Excel group by week (every 7 days starting on a Monday).

Click OK.

 

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:

This will create an Excel pivot table group by week!

 

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!

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.

 

STEP 1: Go to Insert > PivotTable.

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

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

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

This is how the PivotTable will look like:

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

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

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

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

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

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.

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

Common Hurdles and How to Overcome Them

Dealing with Irregular Time Spans

When dealing with irregular time spans, your pivot tables might start to look like a patchwork quilt. But fear not, you can smooth out the bumps by ensuring continuous dates are present in your data set. Should you stumble upon a week that defies the norm, consider introducing an artificial data point to maintain consistency. Adjust your grouping to maintain weekly batches, even when there’s a public holiday or a company-specific event that disrupts the regular flow of data. By maintaining a consistent rhythm in your date fields, you’ll ensure your pivot table doesn’t miss a beat.

Navigating Around Blank Cells and Errors

Encountering blank cells and errors can be like hitting roadblocks on your data journey. But with a bit of Excel savvy, you can navigate around them smoothly. Tackle blank cells by locating them with Excel’s Go To Special feature, then decide if you want to plug them with dummy data or simply remove the problematic rows. For errors, IFERROR is your trusty companion, replacing pesky error messages with neutral or zero values, keeping your data looking clean and professional. Remember, a pristine dataset is your ticket to a hassle-free Pivot Table experience.

Enhancing Your Pivot Table Reporting

Unlocking Insights with Conditional Formatting

Unlocking insights with conditional formatting is like dabbing color on a monochrome canvas—it brings your data to life. With Pivot Tables, conditional formatting can highlight trends, pinpoint anomalies, and emphasize important figures. Start by applying simple color scales to quickly visualize variations or use data bars for a graphic representation of values. If you’re feeling creative, try applying custom rules to spotlight specific weeks where values exceed targets or dip below benchmarks. Remember, the goal is to make your data not just interpretative but also visually compelling.

Mastering Multiple Date Grouping for Comprehensive Reviews

To become adept at multiple date grouping is to hold the keys to a kingdom of data comprehension. Start off by establishing a solid foundation—group your data by months and years for a sweeping overview. Then, zoom in on the specifics: add layers by grouping by weeks within those months. This technique reveals patterns and trends with a richness and depth that simple date groupings can’t touch. Think of it as viewing your data through a multifaceted lens, where each twist brings a new dimension into focus, offering a comprehensive understanding of fluctuations over time.

FAQ

How do I set Monday as the start of the week in an Excel Pivot Table?

To set Monday as the start of the week in an Excel Pivot Table, first, create your pivot table. Then, right-click on a date within your Row Labels, select ‘Group’, and when the ‘Grouping’ dialogue box appears, set the ‘Start at’ date to the Monday of the week you wish to start from. Ensure ‘Days’ is selected and ‘Number of days’ is set to 7. Click ‘OK’ to apply.

What if my data isn’t grouping correctly in Excel Pivot Tables?

If your data isn’t grouping correctly in Excel Pivot Tables, it’s often due to non-date formats, blank cells, or previously grouped items in your data set. First, ensure all date fields are properly formatted as dates. Use Excel’s ‘Text to Columns’ feature to convert any text to dates. Then, look for and fill blank cells. If you’re still facing issues, remove any previously grouped items from your field list and try grouping again.