How to Group by Week in Excel Pivot Table?

Get FREE Advanced Excel Exercises with Solutions!

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.

3 Easy Methods to Insert Pivot Table Group by Week


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.

Set 7 Days as Week to Group Pivot Table by Week

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 instructions given below to make a pivot table group by week:

Set 7 Days as Week to Group Pivot Table 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.

Set 7 Days as Week to Group Pivot Table by Week

  • 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 the 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 by Month in Excel Pivot Table


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:

Use 4 Week Periods to Group Data in Pivot Table

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

Use 4 Week Periods to Group Data in Pivot Table

  • 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.

Use 4 Week Periods to Group Data in Pivot Table

  • So, we can see the pivot table is filtered by a 4-week period.

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


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:

Insert a Helper Column to Group Pivot Table by Week

  • First, select cell D5. Insert the following formula:
=YEAR(C5)&"-"&TEXT(WEEKNUM(C5,2),"00")
  • Press Enter.

Insert a Helper Column to Group Pivot Table by Week

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

Insert a Helper Column to Group Pivot Table by Week

🔎 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.

Insert a Helper Column to Group Pivot Table by Week

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

Insert a Helper Column to Group Pivot Table by Week

  • 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.

Insert a Helper Column to Group Pivot Table by Week

  • 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 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 Group by Week and Month in Excel Pivot Table


How to 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.

Use Right-Click Option

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

Use Right-Click Option

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


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.

With PivotTable Analyze Tab

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

With PivotTable Analyze Tab

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


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 to 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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mukesh Dipto
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo