Pivot Tables not accepting new data

Joe J 20 Reputation points
2026-11-03T15:43:30.3766667+00:00

With each bi-weekly payroll run, I've been adding payroll data into my workbook that contains the running data and pivot tables for various categories--gross wage; FICA; etc. With the last payroll, the data is not refreshing as it has been lo these many months.

It appears that the data table will not expand to capture the new rows (about 400 of them each pay period.) I tried expanding the source table range, but then received this error message: "The data source of a PivotTable connected to filter controls that are also connected to other PivotTables cannot be changed. To change the data source, first disconnect the filter controls from this PivotTable or from the other PivotTables." (See attached image.)

What do I do to get this running again?

Microsoft 365 and Office | Excel | For business | Windows
0 comments
Answer accepted by question author
Lin V 1,875 Reputation points Independent Advisor
2026-11-03T18:17:27.8033333+00:00

Hi Joe,

The issue might be happening because your PivotTables appear to be connected to one or more Slicers (filter controls) that are shared across multiple PivotTables.

When PivotTables share the same slicer connection, Excel typically prevents you from changing the data source of just a single table, which is likely why you are seeing that error message.To help better understand the issue, could you confirm whether:

  • The source data is formatted as an Excel Table (Insert > Table)?
  • The latest ~400 payroll rows are actually included within the table boundaries?
  • You have tried Data > Refresh All after adding the new records?

If the PivotTable is based on an Excel Table, new rows are typically included automatically when the PivotTable is refreshed.

In the meantime, you may also try the following:

1/ Click anywhere in the source data and verify that the latest payroll rows are actually part of the Excel Table. If the new records were pasted outside the table boundaries, the PivotTables will not include them even after a refresh.

2/ If the new rows are not part of the table, resize the table to include all records, then go to Data > Refresh All and check whether the PivotTables update correctly.

3/ If you need to change the PivotTable data source manually, you'll first need to disconnect any shared slicers:

Since this workbook appears to contain a significant amount of payroll history, it may be worth creating a copy of the file and testing any changes there first before modifying the original workbook.

  • Select a slicer.
  • Open Report Connections (or PivotTable Connections).
  • Temporarily remove the connection to the affected PivotTable.
  • Change the data source, refresh the PivotTable, and then reconnect the slicer.

For reference: Use slicers to filter data.

Feel free to return with the answers to the questions above. The more details you can provide, the easier it will be for the community to help identify the cause and suggest the appropriate solution.

Was this answer helpful?

1 person found this answer helpful.
0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joe J 20 Reputation points
    2026-11-03T18:27:03.6466667+00:00

    Believe it or not, I tried all the options you suggested.

    Nothing worked at first.

    I went into each pivottable--there are separate ones for each of the expense types (gross wages, FICA, benefits, etc.) and changed the data ranges, as the new data was, indeed, outside of the previous range area. I also deleted the slicer, and re-entered it.

    Having done all that, I was able to restore my workbook to perform as expected/needed.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.