Pivot Table Issues

How to Group PivotTable Data by Week in Excel Data Model

Camila Milosovich Sep 30, 2026 869 views

Question details

The user needs to group PivotTable data by weeks, but the standard date grouping option is disabled because the PivotTable is built using the Excel Data Model.

How to Group PivotTable Data by Week Using the Excel Data Model

Product: Excel
Device & OS: not provided
Scenario: Analyzing monthly and weekly data sets using a PivotTable generated from an Excel Data Model.
Observed behavior: The standard option to group dates by a specific number of days or weeks is unavailable or grayed out in the PivotTable interface.

Before you start

Ensure you have access to the Data Model relationships feature or the Power Pivot add-in enabled in your spreadsheet application.

Solution 1Recommended

Use a Separate Calendar Table to Group Dates

Since standard grouping is disabled for Data Model PivotTables, you must create a separate Calendar table containing a week number column and establish a relationship with your main data.

When you check 'Add this data to the Data Model' while creating a PivotTable, Excel disables the native date grouping features. To bypass this limitation, you need to create a dedicated Calendar table that defines the weeks and link it to your primary data table.

This approach not only solves the grouping issue but also provides more robust time-intelligence capabilities for your Data Model.

Create a Calendar Table

In your workbook, create a new worksheet and build a table containing a continuous list of dates. Add a second column that calculates the week number for each date using the =WEEKNUM() function.

Add to the Data Model

Select your newly created Calendar table, navigate to the Power Pivot tab on the ribbon, and click 'Add to Data Model'.

Establish a Relationship

Open the Data Model window and go to the Diagram View. Drag the 'Date' column from your main data table to the 'Date' column in the Calendar table to create a many-to-one, single-direction relationship.

Build the PivotTable

Insert a PivotTable directly from the Data Model. In the PivotTable Fields pane, drag the 'Week' field from your Calendar table into the Rows or Columns area to successfully group your data by week.

Use a Separate Calendar Table to Group Dates

Continuous Dates Required: Your Calendar table must contain a continuous, uninterrupted list of dates that covers the entire date range of your main data table for the relationship to function properly.

Free Microsoft Office alternative

Try WPS Office for Simplified PivotTable Management

Struggling with complex Data Models in Excel? WPS Office offers a free, lightweight spreadsheet tool with an intuitive PivotTable interface. Standard date grouping by days, weeks, and months is fully supported natively, helping you analyze data without convoluted workarounds.

1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your computer.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx file. Your original data formatting and layout will be preserved.
3. Group Dates Natively: Select your data range, click on the Insert tab, and choose PivotTable. Right-click any date in the PivotTable and select 'Group' to easily group by weeks natively.

Easily group PivotTable dates by weeks or months without relying on complex Data Models.Fully compatible with Microsoft Excel (.xlsx, .xls) files and standard PivotTable structures.Lightweight software that launches instantly and runs smoothly on older devices.Familiar user interface ensuring a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the grouping option disabled in my PivotTable?

If your PivotTable was created by checking 'Add this data to the Data Model' (using Power Pivot), the standard date grouping feature is automatically disabled by the software. You must use a connected Calendar table to group dates instead.

Can I group PivotTable data by weeks without a Calendar table?

Yes, but only if you create a standard PivotTable directly from a single worksheet data range. You must avoid adding the data to the Data Model during the PivotTable creation process to retain native grouping features.

How do I calculate the week number in my data?

You can use the WEEKNUM function. Simply create a new column and enter =WEEKNUM(A2) (assuming A2 contains your date) to return the numerical week of the year for that specific date.

Does the Calendar table need to have continuous dates?

Yes. For time-intelligence functions and relationship mapping to work correctly in a Data Model, your Calendar table must contain a continuous, uninterrupted list of dates spanning your entire dataset.

Excel & Data Problems