Pivot Table Issues

How to Change Excel PivotTable Data Source to a Dynamic Range

Adam Davis Oct 20, 2026 868 views

Question details

The user needs to switch an Excel PivotTable data source from an OFFSET-based dynamic named range to a Data Model table without clearing the PivotTable or retaining ghost values in slicers.

How to Change the Data Source for an Excel PivotTable Using a Dynamic Range

Product: Microsoft Excel
Device & OS: not provided
Scenario: Updating a PivotTable to handle dynamic data expansion and text columns using the Data Model, while ensuring slicers accurately reflect the active data.
Observed behavior: Changing the source directly clears the existing PivotTable, and slicers continue to display old or blank values (e.g., <2021) even when no matching records exist in the updated source.

Before you start

Verify that your data columns have clear, unique headers, and review any active slicers connected to your current PivotTable before altering the data source.

Solution 1Recommended

Use an Excel Table Instead of an OFFSET Named Range

Converting your raw data into a native Excel Table is the most reliable way to create a dynamic source for PivotTables and Data Models without using volatile OFFSET formulas.

Excel Tables automatically expand when new rows or columns are added. This eliminates the need for complex COUNTA and OFFSET named ranges, which can cause calculation overhead and compatibility issues when importing to the Data Model.

Format Data as a Table

Select any cell within your raw data range. Go to the 'Insert' tab on the ribbon and click 'Table' (or press Ctrl+T). Ensure 'My table has headers' is checked and click OK.

Name the Table

Click anywhere inside the new table. Go to the 'Table Design' tab and type a recognizable name (e.g., 'SalesData') into the Table Name box on the far left.

Update PivotTable Data Source

Click anywhere inside your existing PivotTable. Navigate to the 'PivotTable Analyze' tab and click 'Change Data Source'. In the dialog box, type your new table name ('SalesData') and click OK.

Add to Data Model (Optional)

If your PivotTable requires the Data Model for distinct counts or multiple tables, insert a new PivotTable, select your table, and check the box for 'Add this data to the Data Model'.

Use an Excel Table Instead of an OFFSET Named Range

Automatic Expansion: Any future records added to the bottom of the Excel Table will automatically be included in the PivotTable upon refreshing.

Solution 2

Clear Ghost Values from PivotTable Slicers

If your slicers are showing blank items or old values (like <2021) that no longer exist in the data source, you need to adjust the slicer and PivotTable retention settings.

Manage Dynamic PivotTables Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful and intuitive PivotTable features. You can easily format your data as a Table to create dynamic ranges, ensuring your reports update automatically without the complexity of OFFSET formulas.

1. Format Data as a Table: Highlight your dataset in WPS Spreadsheet, go to the Home tab, and select 'Format as Table'.
2. Insert PivotTable: Navigate to the Insert tab, click 'PivotTable', and ensure your newly created Table is selected as the source range.
3. Add Slicers: In the PivotTable Options tab, click 'Insert Slicer' to visually filter your data streams easily.
4. Refresh Data: Simply add new data to the bottom of your table, right-click the PivotTable, and select 'Refresh' to update your insights.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structuresNative support for formatting data as Tables for automatic dynamic rangesIntuitive Slicer management to filter data without retaining deleted ghost valuesLightweight, fast, and completely free for everyday data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable disappear when I change the data source to the Data Model?

Switching a standard PivotTable to a Data Model source often clears the layout because the underlying data architecture changes from a simple range to an OLAP cube. You will usually need to rebuild the rows, columns, and values using the new Data Model fields.

Why should I use an Excel Table instead of the OFFSET function?

Excel Tables automatically expand to include new data without requiring volatile formulas. Volatile functions like OFFSET recalculate every time any change is made in the workbook, which can significantly slow down performance in large files.

How do I refresh the Data Model in Excel?

You can refresh the Data Model by navigating to the 'Data' tab on the ribbon and clicking 'Refresh All'. Alternatively, you can open the Power Pivot window and click 'Refresh' directly within the Data Model interface.

Can I remove the '(blank)' option from my PivotTable slicers?

Yes. Right-click the slicer, select 'Slicer Settings', and check 'Hide items with no data'. Also, ensure that your data source range does not include empty rows at the bottom, which is a common issue when referencing entire columns (e.g., A:A).

Excel & Data Problems