Pivot Table Issues

How to Fix Excel PivotTable Duplicate Years After VBA Refresh

Elise WilliamsElise Williams Nov 13, 2026 868 views

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.

How to Fix Excel PivotTable Duplicate Years After VBA Refresh
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 you start

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.

Solution 1Recommended

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.

1
Save and close the workbook

Save the workbook, close Excel completely, and then restart the computer if the issue has persisted for several days.

2
Open the workbook and open the VBA editor

Open the workbook, press Alt+F11 to open the Visual Basic for Applications editor, and locate the module containing your refresh macro.

3
Run a minimal PivotTable refresh loop

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.

4
Verify the year labels

Click the PivotTable year field dropdown and confirm each year appears once instead of twice.

Restart Excel and Refresh PivotTables with a Minimal VBA Loop
Duplicates cleared: If the duplicates disappear, add this simple refresh loop to your existing VBA procedure and re-test the full automation.
Free Microsoft Office alternative

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.

Open and edit .xlsx workbooks with PivotTables without losing Excel formattingRefresh PivotTables directly in WPS Spreadsheets without relying on VBA or Power QueryFree, lightweight Office suite with a familiar ribbon interfaceSeamless migration from Microsoft Excel to WPS Office
microsoft office alternative - wps office

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.