Workbook with several pivot tables will not accept new data

Dear @Joe J,

From description and my research, this is a very common Excel issue, especially in Microsoft 365 and recent versions after a workbook has been renamed, copied, or saved as a new file. As a forum moderator, I genuinely wish I could directly access your account or delve into the backend systems to diagnose and fix this for you. However, our role here is limited to providing general guidance and solutions that can be applied by users. 

The good news is that this issue is usually easy to fix. Please try the steps below, which resolve most cases like this:

Fix the PivotTable data source reference

1. Click any cell inside one of your PivotTables.
2. Go to the PivotTable Analyze tab (or Options tab in older Excel) → Change Data Source.
3. In the dialog box, look at the Table/Range field. It will probably show something like:'[OldPayrollFileName.xlsx]YourDataSheet'!$A$1:$Z$10000orOldFileName.xlsx!Table1
4. Manually delete the old filename part (including the square brackets [ ] and the exclamation mark if it appears right after). Leave just the sheet name + range, for example:YourDataSheet!$A$1:$Z$10000(Or better click the small arrow button on the right of the box and re-select your current data range with the mouse on the actual sheet.)
5. Click OK.
6. Go to the Data tab > Refresh All.

Repeat for any other PivotTables that still error (or just do Refresh All again ,most share the same cache and will update).

If the Change Data Source dialog won't open or immediately errors, try these in order:

Force a fresh reference with Save As

• File > Save As > give it a completely new name (e.g., PayrollAnalyzer_v2.xlsx).
• Close the file completely.
• Re-open the new file and try Change Data Source again. This often resets the internal reference.

Prevent the issue in the future by using an Excel Table

1. Go to your raw payroll data sheet.
2. Select the entire data range (including headers).
3. Press Ctrl + T (or Insert > Table) > OK.
4. In the Table Design tab that appears, change the Table Name to something clear like PayrollRawData.
5. Now go back to each PivotTable > Change Data Source > instead of a range, type or select your new table name (PayrollRawData).
6. Refresh All.

Adding new rows in the future is now automatic, just paste below the table and refresh. No more range adjustments.

If the issue still persists:

• Copy your raw data sheet to a brand-new blank workbook.
• Create fresh PivotTables from that clean data.
• Copy your existing PivotTable layouts over (or rebuild the fields, usually quick once the source is clean).
• Delete the old broken PivotTables.

Additional checks (if needed)

• Data tab > Queries & Connections. If you see the old filename listed, select it and click Break Link.
• File > Info > Check for Issues > Inspect Document (remove any old document properties or links).
• As a last resort: File > Open > browse your file > click the arrow next to Open > Open and Repair.

After applying these steps, especially removing the old filename reference or switching to an Excel Table, you should be able to add new payroll rows, refresh the data, and generate your cost‑center reports without issues.

I hope this information is helpful. Please follow these steps and let me know if it works for you. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you. Thank you for your patience and understanding. I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

The behavior matches known PivotTable issues that occur when Excel cannot correctly resolve the workbook or data source reference.

Use these checks and fixes:

1. Check the workbook file name

• If the workbook name contains square brackets, for example Payroll[1].xlsx, Excel can show errors like “Data source reference is not valid” and fail to work correctly with PivotTables.
• Rename the file so it has no square brackets or other invalid characters (for example, Payroll_2024.xlsx).
• Close Excel, reopen the renamed workbook, and try Refresh All again.

2. Confirm the PivotTable data source is valid

• In each PivotTable, go to PivotTable Analyze → Change Data Source.
• Verify that the Table/Range points to a valid range or table in the current workbook.
• If the source range refers to a sheet or range that no longer exists (for example, after renaming sheets or moving data), reselect the correct range and click OK.

3. If the workbook is opened from a browser or temporary location

• When opening from a browser (for example, from email or a web app), choose Save instead of Open, then open the saved file from a local folder.
• This avoids the browser renaming the file with square brackets in a temporary directory, which can break PivotTable references.

4. If PivotTables appear read-only or not interactive

• If Excel shows a message like “Some PivotTable features aren't compatible with this version of Excel,” open the workbook in Excel for the web or a fully updated Microsoft 365 desktop Excel and then refresh/recreate the PivotTables as needed.

If, after renaming the file and re-pointing the data source, the error persists, create a new workbook, copy the raw payroll data into a new table, and rebuild one PivotTable from scratch to confirm that new PivotTables work correctly with the same data.

References:

• Excel PivotTable error "Data source reference is not valid"
• PivotTable is read-only