# How to Group by Week in an Excel Pivot Table? – 3 Methods

The following dataset showcases sales amounts for January.

## Method 1 – Setting 7 Days As a Week to Group a Pivot Table by Week

This is the pivot table of the previous dataset. The group selection method will be used, counting the number of days as 7.

The week count will start from Â January, 3.

• Select any date in the pivot table.
• Right-click.
• Select Group.

• In the dialog box, enter starting date 3-01-2022.
• Select Days.
• Enter 7 in Number of Days .
• Click OK.

• The pivot table is grouped by week.

## Method 2 – Using 4-Week Periods to Group Data in a Pivot Table

Group data of whole months:

• Select any date in the pivot table.
• Right-click.
• Select Group.

• In the dialog box, enter starting date 3-01-2022.
• Select group by Days.
• Enter 28 in Number of days.
• Click OK.

• The pivot table is filtered by a 4-week period.

## Method 3 – Inserting a Helper Column to Group a Pivot Table by Week

A new column (Week) was inserted in the previous dataset.

• Select D5. Use the following formula:
`=YEAR(C5)&"-"&TEXT(WEEKNUM(C5,2),"00")`
• Press Enter.

• The number of weeks is displayed in C5.

Formula Breakdown

• WEEKNUM(C5,2),”00″: Â returns the number of weeks of the date value in C5.
• TEXT(WEEKNUM(C5,2),”00″: Extracts the text value of the week.
• YEAR(C5)&”-“&TEXT(WEEKNUM(C5,2),”00”: Returns the value of week with Year.
• Drag down the Fill Handle or double-click the (+) sign to get the number of weeks for all dates.

• This isÂ  the output.

• To create a pivot table, select any cell from the data range. Here, D4.
• Go to the Insert tab and select Pivot Table.
• Choose From Table/Range.

• In the dialog box, check New Worksheet and click OK.

• A new window will be displayed.

• Drag WeekÂ and drop it in the first place of the section Rows.
• Drag DateÂ and drop it in the second place of the section Rows.
• Drag Sales AmountÂ and drop it in the â…€ Values section.

• A pivot table group by week is displayed.

## How to Ungroup Week Data in a Pivot Table?

### 1. Using the Right-Click Option

TheÂ  following dataset is grouped by week.

• Select any cell in the pivot table.
• Right-click.
• Select Ungroup.

• Data in the pivot table will be ungrouped.

### 2. With the PivotTable Analyze Tab

• Select any cell from the data range.

• Go to the PivotTable Analyze tab.
• In Group, select Ungroup.

• The pivot table is ungrouped.

## Things to Remember to Troubleshoot Errors

To avoid errors:

1. Create a group with a minimum of two.
2. Make sure there are no blank cells.
3. Do not enter a text value in a date or numeric field or vice versa.

Download Practice Workbook

You can download the practice workbook here.

## Related Articles

<< Go Back to Group Dates in Pivot Table | Group Pivot Table | Pivot Table in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mukesh Dipto

Mukesh Dipto is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, HTML, CSS, JavaScript, and WordPress. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF