Pivot Table Issues

How to Prevent Excel PivotTables from Sharing Date Grouping

Adam Davis Sep 30, 2026 868 views

Question details

The user needs to group date fields independently in multiple PivotTables that are generated from the exact same data source.

How to Prevent Excel PivotTables from Sharing Date Grouping

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating multiple PivotTables from a single dataset and attempting to apply different date grouping intervals (e.g., weekly vs. monthly) to each.
Observed behavior: By default, grouping a date field in one PivotTable automatically forces the same grouping change in all other PivotTables because they share the same underlying PivotCache.

Before you start

Ensure your source data range is properly formatted as a continuous table without blank rows or columns. Note that forcing separate PivotCaches will slightly increase your workbook's overall file size.

Solution 1Recommended

Use the PivotTable Wizard Shortcut (Alt+D+P)

Use the legacy PivotTable and PivotChart Wizard to intentionally bypass Excel's default cache-sharing behavior and generate an independent PivotCache.

When you use the standard 'Insert > PivotTable' command, Excel automatically optimizes memory by sharing the PivotCache among all PivotTables connected to the identical data source. Using the legacy wizard gives you the option to refuse this shared cache, keeping your groupings independent.

Select the Data Range

Click anywhere inside your source dataset that you wish to use for the new PivotTable.

Open the Legacy Wizard

Press the keys 'Alt', 'D', and 'P' sequentially on your keyboard to launch the classic PivotTable and PivotChart Wizard.

Configure Data Source

In step 1 of the wizard, choose 'Microsoft Excel list or database' and click 'Next'. In step 2, verify your data range and click 'Next'.

Decline the Shared Cache

If Excel detects an existing PivotTable based on this data, a prompt will ask if you want to base the new PivotTable on the same data to save memory. You must click 'No' to create a separate PivotCache.

Finish and Group

Choose where to place the new PivotTable (e.g., New Worksheet) and click 'Finish'. You can now right-click your date fields to group them (e.g., into seven-day weeks) without affecting the original PivotTable.

Independent Grouping Achieved: Because the new PivotTable relies on a separate cache, changes to grouping, calculated items, and calculated fields will remain entirely independent.

Solution 2

Use a Named Range or Excel Table with a Different Name

Trick Excel into creating a separate PivotCache by referencing the exact same data through a differently named source.

Powerful Data Analysis Tool

Create and Manage PivotTables Easily with WPS Spreadsheet

WPS Office provides a highly intuitive Spreadsheet application that fully supports complex data analysis, including PivotTables and custom date grouping, giving you seamless control over your reports.

1. Open Your Data: Launch WPS Spreadsheet and open your existing data file.
2. Insert PivotTable: Highlight your dataset, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
3. Configure the Table: Select the destination for your new PivotTable and drag your required fields into the Rows, Columns, and Values areas.
4. Group Dates Independently: Right-click the date values within the PivotTable, select 'Group', and customize your date intervals exactly as needed.

Fully compatible with Microsoft Excel (.xlsx, .xls) and its PivotTable cache structures.Intuitive interface for creating, grouping, and managing PivotTables without steep learning curves.Lightweight architecture that processes large datasets smoothly without crashing.Free to use with comprehensive built-in templates for professional reporting.

microsoft office alternative - wps office

Frequently Asked Questions

Why do my PivotTables update together when I only change one?

By default, Excel links PivotTables originating from the same data source to a single underlying PivotCache. This is done to optimize memory and reduce the overall file size. Because of this shared cache, changing a date grouping or adding a calculated field in one table will automatically reflect in all connected tables.

Can I unlink an existing PivotTable without recreating it?

Unfortunately, once a PivotTable is created and shares a cache, you cannot simply 'unlink' the cache. You will need to recreate the specific PivotTable using the Alt+D+P method, or temporarily copy the source data to a new sheet to force a new cache creation upon insertion.

Does creating multiple PivotCaches increase my file size?

Yes, each separate PivotCache stores its own compressed, background copy of the source data. While creating a separate cache allows independent date grouping, it will marginally increase the overall size of your Excel workbook, especially if the source dataset is very large.

Does the Alt+D+P shortcut work in newer versions of Excel?

Yes. Even though the PivotTable and PivotChart Wizard is considered a legacy feature from older versions of Excel, Microsoft has kept the Alt+D+P shortcut functional in all modern versions of Excel, including Microsoft 365, specifically for advanced cache management.

Excel & Data Problems