Pivot Table Issues

Copy Data Between Excel Sheets and Refresh a Pivot Table

Phi Hung VoPhi Hung Vo Nov 18, 2026 872 views

Question details

Copy or reference data from one worksheet on another and keep a pivot table up to date when the source changes.

Copy Data Between Excel Sheets and Refresh a Pivot Table
Product
Microsoft Excel
Device & OS
not provided
Scenario
Data is stored on Sheet A, mirrored on Sheet B, and summarized in a pivot table.
Observed behavior
Worksheet references can update when source data changes, but the pivot table does not normally refresh automatically.
Before you start

Identify the source data range, its column headers, and the worksheet containing the pivot table. If Sheet B only duplicates Sheet A, consider using Sheet A directly as the pivot table source.

Solution 1Recommended

Use an Excel Table as the pivot source and refresh manually

Use this approach when Sheet B is not needed as an intermediate copy. An Excel Table can expand as you add source rows.

1
Create a source table

On Sheet A, select the data and headers, then choose Insert > Table. Confirm that the table has headers and select OK.

2
Set the pivot source

Select the pivot table, open PivotTable Analyze > Change Data Source, and enter or select the table on Sheet A. Choose OK.

3
Refresh the pivot table

After changing or adding source data, select the pivot table and choose PivotTable Analyze > Refresh, or use Data > Refresh All.

Use an Excel Table as the pivot source and refresh manually
Refresh is still required: An expanding source table helps include new rows, but it does not make the pivot table refresh automatically.
Work with spreadsheets in WPS Office

Copy worksheet data and refresh a pivot table in WPS Spreadsheet

Use cell references to display data from another worksheet, then refresh the pivot table after the source changes.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Reference the source: On Sheet B, enter =A!A1 in cell A1, then drag the fill handle across and down to cover the source data.
  3. 3. Check the pivot source: Select the pivot table and check that its data source covers the populated range on Sheet B, including the headers.
  4. 4. Refresh the pivot: Right-click the pivot table and choose Refresh after changing the source data. Update the source range if new rows extend beyond it.
Reference data across worksheets and refresh pivot summariesOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceGet started with WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my Excel pivot table update when a source cell changes?

A pivot table normally requires a refresh after source data changes. Select it and choose PivotTable Analyze > Refresh, or use Data > Refresh All.

Do I need Sheet B if it only copies Sheet A?

Not necessarily. Select the pivot table and use PivotTable Analyze > Change Data Source to point it directly to the source data or an Excel Table on Sheet A.

Will an Excel Table automatically include new rows in a pivot table?

An Excel Table can expand to include rows added to it, but you still need to refresh the pivot table. Select the pivot table and choose PivotTable Analyze > Refresh.

Can a VBA worksheet event detect changes to formula results?

Worksheet_Change does not run merely because a formula recalculates. It is intended for cell changes such as direct edits; formula-driven changes require a different approach.

Can I use a dynamic array instead of a pivot table?

In a version of Excel that supports dynamic arrays, functions such as FILTER can produce an updating list when interactive pivot-table features are not needed.