Excel Pivot Table Group by Week (3 Suitable Examples)

In Microsoft Excel, a pivot table is a table of categorized values. In this article, we will learn about the pivot table group by week. With the use of a pivot table, we can summarize, sort, reorganize, group, count, total or average data stored in a table. We will illustrate 3 methods to group a pivot table by week. Also, we will demonstrate the process of ungrouping a pivot table for your convenience.

First and foremost, we will introduce with dataset that we will use for this article. We have the following dataset of sales amounts for January. In the below image, we take only values for 13 days. The arrow sign indicates that we have more values in this dataset. You can access the full dataset by downloading the practice workbook added to this article.

1. Setting 7 Days As Week to Group Pivot Table by Week

Now, In this example, we can see the pivot table of our previously mentioned dataset. We will group a pivot table by week using the group selection method. Here we will count the number of days as 7.

Before we step further to solve this problem we want you to look at the image below. From the below image, we can see that the first weekday of January 2022 is 3 January. The day is Monday. So, basically, our week count will start from 3 January. Now follow the instruction given below to make a pivot table group by week:

• In the beginning, select any date from the pivot table.
• Next, do a right-click.
• Then, select the option Group from the available options.

• A new dialogue box will open.
• In the box, input starting date 3-01-2022.
• Select the option group by Days.
• Input the value of Number of Days 7.
• Press OK.

• Finally, we get our pivot table grouped by week. This table also gives the total amount of sales for each week.

Read More: How to Group Pivot Table by Month in Excel

2. Using 4 Week Periods to Group Data in Pivot Table

In this example, we will continue with our previous pivot table. But we will group the data of whole months for 4 4-week period. Let’s see the necessary steps to perform this action:

• Firstly, select any date from the pivot table.
• Next, do a right-click.
• After that, from the available options select the option Group.

• Now we can see a new dialogue box.
• In the box, input starting date 3-01-2022.
• Select the option group by Days.
• Use the value 28 for the section Number of days.
• Then press OK.

• So, we can see the pivot table is filtered by 4 weeks period.

Read More: How to Group Dates by Filter in Excel (3 Easy Methods)

3. Inserting a Helper Column to Group Pivot Table by Week

Another interesting way to group a pivot table by week is to insert a helping column. In the following figure, we can see a new column with our previous dataset. The name of the new column is week. We will sort our dates by week in this column. After sorting we will group our data with the help of this helping column. Follow the step-by-step tutorial below to perform this action:

• First, select Cell D5. Insert the following formula:

• Press Enter.

• Here, we get the week number to the date in cell C5.

🔎 How Does the Formula Work?

• WEEKNUM(C5,2),”00″: This part simply returns the week number of the date value in cell C5.
• TEXT(WEEKNUM(C5,2),”00″: Extract the text value of the week.
• YEAR(C5)&”-“&TEXT(WEEKNUM(C5,2),”00”: Return the value of week with Year.
• Drag the Fill Handle tool to the end of the dataset to get the week number for all the dates. We can also do this by double-clicking on the (+) sign of Fill Handle.

• In the below image, we can see the week number for all the dates.

• Now to create a pivot table including the new helping column select any cell from the data range. In this example, we are selecting cell D4.
• Next, go to the Insert tab and select the option Pivot Table.
• From the drop-down values select the option From Table/Range.

• Now, a new dialogue box will open. Excel will automatically select the Table/Range for you.
• Check the option New Worksheet and press OK.

• As a result, we see the following section to fix the parameters of the pivot table.

• Now set the parameters for the pivot table like shown in the image below. Drag the Week option and drop it in the first place of the section Rows.
• Then, drag the Date option and drop it in the second place of the section Rows.
• After that, drag the Sales Amount option and drop it in the ⅀ Values section.

• So, we get a pivot table group by week for our new dataset along with the helping column.

Read More: How to Use Excel Pivot Table to Group Dates by Month and Year

Ungroup Week Data in Pivot Table

Suppose, we have grouped a pivot table by week. Now we need to ungroup the table again. It can happen frequently when you practice with real-time datasets. In this section, we will demonstrate to you two methods to ungroup a pivot table.

1. Using Right-Click Option

In the below image, we can see a dataset grouped by week. We will ungroup the table by using the right-click option. Just do the following steps to perform this action.

• In the beginning, select any cell in the pivot table.
• Next, do right-click.
• After that, select the Ungroup option from the available options.

• So, we get a new pivot table without grouping.

Read More: How to Group Dates in Pivot Table (7 Ways)

2. With PivotTable Analyze Tab

To ungroup a pivot table besides using the right-click option, we can also use the PivotTable Analyze tab. We will ungroup the following dataset by using the PivotTable Analyze tab. Let’s see the steps to do this:

• Firstly, select any cell from the data range.

• Next, go to the PivotTable Analyze tab.
• Select the option Ungroup under the Group section.

• Finally, we get a new pivot table that does not have a grouping.

Read More: [Fix] Cannot Group Dates in Pivot Table: 4 Possible Solutions

Things to Remember to Troubleshoot Errors

While working with the pivot table, sometimes we may encounter errors. There may be different kinds of reasons behind showing an error. Some common reasons are-

1. To create a group we must select a minimum of two or more entries. We can not create a group with a single entry.
2. If our dataset contains blank cells, we will face an error message for this.
3. We will also get an error message if we input a text value in a date or numeric field or vice versa.

So, if you face an error while grouping the pivot table check the above possibilities and fix the problem.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

In this article, we have illustrated the methods to create a pivot table group by week. Download the practice workbook added with this article and do practice yourself for the best outcome. If you feel any kind of confusion just comment in the below box our team will reply to you as early as possible. Visit our website Exceldemy to know about the more interesting functions of the pivot table.

Related Articles

• How to Group by Year in Excel Pivot Table (3 Easy Methods)
• How to Group Dates in Excel Slicer (4 Ways)
• Excel Pivot Table Auto Grouping by Date, Time, Month, and Range!
• How to Group Data by Month in Excel (2 Useful Methods)
• Group Dates in Excel Chart (3 Easy Ways)
• How to Group Pivot Table by Month in Excel (2 Methods)