How to Fix Excel PivotTable Duplicate Years After VBA Refresh
Question details
The user needs to resolve duplicate year labels that appear in a Microsoft Excel PivotTable connected to Power Query data after running a VBA refresh, while manual refresh works correctly.

- Product
- Microsoft Excel with Power Query
- Device & OS
- not provided
- Scenario
- The user automates workbook refresh with VBA, but the PivotTable shows each year twice after the refresh even though the date values match.
- Observed behavior
- Duplicate year entries appear in the PivotTable after a VBA refresh, while manual refreshes work correctly.
Before changing VBA or PivotTable settings, confirm that the Power Query source outputs unique date values and note whether duplicates appear only after VBA refresh, only after manual refresh, or both.
Restart Excel and Refresh PivotTables with a Minimal VBA Loop
Use this first when duplicate years appear only after a VBA refresh, because a stale Excel session can cause refresh artifacts.
This clears the cached PivotTable state without changing your data model or VBA project.
Save the workbook, close Excel completely, and then restart the computer if the issue has persisted for several days.
Open the workbook, press Alt+F11 to open the Visual Basic for Applications editor, and locate the module containing your refresh macro.
Run a basic loop such as: For Each pvtTbl In ActiveSheet.PivotTables: pvtTbl.RefreshTable: Next, then switch back to the sheet and check the year field.
Click the PivotTable year field dropdown and confirm each year appears once instead of twice.

Clear Retained PivotTable Items and Check Power Query Data Types
Use this when restarting Excel does not help and duplicate year labels remain after refresh.
Reorder the VBA Refresh Sequence to Avoid Duplicate Year Labels
Use this when your macro refreshes every connection and PivotTable but duplicates still appear under specific timing.
Simplify PivotTable Workflows in WPS Spreadsheets
If Excel VBA and Power Query refresh issues keep interfering with your PivotTable reports, WPS Spreadsheets offers a free, lightweight alternative with familiar pivot table tools, strong Microsoft Excel file compatibility, and a simple refresh workflow.

Frequently Asked Questions
Why does my Excel PivotTable show duplicate years after a VBA refresh?
This is usually caused by a stale PivotTable cache, retained old items, or a VBA refresh sequence that refreshes Power Query data and the PivotTable in an unexpected order. Start by restarting Excel and running a minimal PivotTable.RefreshTable loop.
How do I force a PivotTable refresh with VBA in Excel?
Use a loop over ActiveSheet.PivotTables and call pvtTbl.RefreshTable for each table. For synchronous data refresh, set BackgroundQuery to False and refresh the PivotCache before the table.
How do I remove duplicate year labels from an Excel PivotTable?
Right-click the PivotTable, choose PivotTable Options, go to the Data tab, set Number of items to retain per field to None, then refresh. Also check that the Power Query date column is set to Date rather than Date/Time or Text.
Can WPS Spreadsheets open and refresh Excel PivotTables?
Yes. WPS Spreadsheets can open .xlsx workbooks with PivotTables and refresh them using its PivotTable tools, without requiring VBA or Power Query.
Does WPS Office support Excel VBA and Power Query?
WPS Office does not fully support Excel VBA and Power Query. For VBA-heavy or Power Query-dependent workbooks, WPS Spreadsheets is best used for viewing, editing, and pivot reporting in a simpler environment.




