Pivot Table Issues

How to Fix Excel Cannot Change the Source for a PivotTable

Maira Mehtab Sep 21, 2026 868 views

Question details

The user is unable to update or change the source data range for an existing PivotTable in Excel, as clicking "OK" in the dialog box has no effect.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to update the data source for a PivotTable via the "Change Data Source" or "Move PivotTable" dialog.
Observed behavior: Clicking "OK" in the dialog does nothing and fails to update the source, whereas clicking "Cancel" successfully closes the window.

Before you start

Ensure that your source data does not contain any blank column headers and that the workbook or worksheet is not currently protected by a password.

Solution 1Recommended

Verify and Re-select a Valid Data Source

Ensure the source range is valid, accessible, and correctly formatted without blank headers before reapplying it.

Often, the Change Data Source dialog will silently fail to close if the selected range is invalid, contains merged cells in the header row, or has blank column names.

Inspect your source data

Navigate to the source data sheet and confirm it is formatted as a contiguous range or table. Ensure every column has a unique, non-blank text header.

Check network accessibility

If your source data is located on a network drive or external workbook, verify that you are connected to the network and that the external file is accessible.

Access PivotTable Analyze

Click anywhere inside your existing PivotTable to activate the PivotTable Tools. Go to the PivotTable Analyze tab on the ribbon.

Change Data Source

Click 'Change Data Source'. Carefully re-select the valid data range using your mouse, ensuring no blank columns are included, and click OK.

Use Excel Tables: Formatting your source data as an official Excel Table (Insert > Table) and using the Table Name as the source can prevent range-selection errors.

Solution 2

Gather Diagnostics for Further Troubleshooting

If the dialog remains unresponsive, document the exact scenario and test in a clean environment to isolate the cause.

Easily Manage Data with WPS

Use WPS Spreadsheet to Create and Manage PivotTables Seamlessly

WPS Spreadsheet offers a highly compatible and user-friendly interface for managing complex data. You can easily change PivotTable data sources without encountering unresponsive dialog boxes, making data analysis smooth and efficient.

1. Open the workbook: Launch WPS Spreadsheet and open the .xlsx file containing your PivotTable.
2. Select the PivotTable: Click anywhere inside the existing PivotTable to bring up the PivotTable Tools.
3. Navigate to the Options tab: On the top ribbon, click the 'Analyze' or 'Options' tab under PivotTable Tools.
4. Open Change Data Source: Click the 'Change Data Source' button on the toolbar.
5. Update the range: Select the new data range in your worksheet and click OK to instantly refresh the PivotTable.

Fully compatible with Microsoft Excel (.xlsx) formats and PivotTable structuresIntuitive PivotTable creation and data source managementLightweight application with fast processing for large datasetsFree to use for everyday spreadsheet and data analysis tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does clicking OK in the Change Data Source dialog do nothing?

This typically happens if the selected range is structurally invalid for a PivotTable. Common reasons include missing column headers, selecting entire columns that include blank rows at the top, or referencing a disconnected external workbook.

Can a protected worksheet prevent me from changing a PivotTable source?

Yes. If the worksheet containing the PivotTable or the source data is protected, Excel restricts modifications to the data source. Go to the Review tab and click 'Unprotect Sheet' before trying again.

How do I ensure my PivotTable updates automatically when new rows are added?

Instead of selecting a fixed range (like A1:D100), select your source data and press Ctrl+T to format it as an Excel Table. Then, type the Table Name in the Change Data Source dialog. The PivotTable will automatically include new rows when you click Refresh.

Does WPS Office support Excel PivotTables?

Yes, WPS Spreadsheet is fully compatible with Excel PivotTables. You can open, view, edit, and change the data source of PivotTables created in Microsoft Excel without losing data or formatting.

Excel & Data Problems