Pivot Table Stops Refreshing After XLS to XLSX Conversion — How to Fix It

You converted your Excel workbook from .xls to .xlsx. The formulas look right. The data is there. But when you click Refresh on the pivot table — nothing updates, or you get an error. This is one of the most common post-migration headaches, and there are three distinct causes, each with a different fix.

This guide covers all three. Work through them in order and you'll have your pivot tables refreshing correctly again.

Why Pivot Tables Break During XLS to XLSX Conversion

Pivot tables in .xls files store data source references differently than .xlsx files. The older binary format encoded source references in a way that doesn't always translate cleanly to the XML-based .xlsx format — especially when the pivot table pulls from an external file, a named range, or a data connection. Pivot tables are one of several features that need attention during migration; our complete guide to legacy Excel migration covers the full checklist.

Three failure modes cover almost every case:

1. The data source still references the old .xls file path
2. The pivot cache is corrupted or stale
3. An external data connection points to a file that doesn't exist at its original path

Fix 1: Update the Data Source Reference

If the pivot table was pulling data from within the same workbook, converting the file should keep that working — but sometimes the source reference gets stuck pointing to the old file.

Symptom:

Fix:

1. Right-click anywhere inside the pivot table
2. Select PivotTable Options (Excel 365) or PivotTable Options > Data
3. Click Change Data Source
4. Verify that the source range points to the correct sheet and range in the current file (e.g., Sheet1!$A$1:$F$500)
5. If it shows a path like 'C:\Users\...\report.xls'!Sheet1!$A$1:$F$500, update it to point to the current sheet without the file path prefix
6. Click OK, then click Refresh

If you're using a named range as the pivot source, check that the named range still exists and points to the right cells. Go to Formulas > Name Manager and look for any names with #REF! errors.

Fix 2: Clear and Rebuild the Pivot Cache

Every pivot table stores a cached copy of its source data called the pivot cache. During XLS to XLSX conversion, this cache can become corrupt — the pivot table appears to work but shows stale or incorrect data, and Refresh doesn't fix it.

Symptom:

Fix (delete and recreate the pivot table):

1. Note the pivot table's layout — fields in Rows, Columns, Values, Filters areas
2. Select the entire pivot table and press Delete
3. Go to Insert > PivotTable
4. Select the same source range as before
5. Rebuild the layout by dragging fields to the same areas

This forces Excel to build a fresh cache from the current worksheet data. It's more work than a one-click fix, but it's the most reliable way to clear a corrupted cache.

If you have multiple pivot tables on the same cache (a common pattern where several pivot tables share one source), you'll need to recreate all of them. You can check whether pivot tables share a cache by looking at their PivotTable.CacheIndex property in VBA — tables with the same index share a cache.

VBA shortcut for rebuilding multiple pivot tables

If you have many pivot tables on the same sheet, this macro saves time:

Sub RefreshAllPivotCaches()
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
        On Error Resume Next
        pc.Refresh
        On Error GoTo 0
    Next pc
    MsgBox "All pivot caches refreshed."
End Sub

Run this from the Developer tab > Macros. If it errors on a specific cache, that cache has a broken source reference — use Fix 1 to correct it first.

Fix 3: Update External Data Connections

Some pivot tables in .xls files pulled data from an external source: another Excel file, a CSV, an Access database, or a SQL Server connection. When the workbook was converted, the connection string stored inside the file still points to the old .xls source — or worse, the source file was also renamed or moved during the migration.

Symptom:

Fix:

1. Go to Data > Queries & Connections
2. Right-click each connection and select Properties
3. Under Connection String or Command Text, update any file paths to reflect the new location and filename
4. If the source was another .xls file that was also converted, update the path to the new .xlsx filename
5. Click OK, then refresh the pivot table

For Power Query connections (common in Excel 2016+), go to Data > Get Data > Launch Power Query Editor, find the affected queries, and update the source step to point to the new file path.

The Special Case: Multiple Workbooks With Cross-References

When your pivot table pulls from Sheet1 of the same workbook, fixing one file is enough. But many organizations build reporting workbooks that aggregate data from 10, 20, or 50 separate source files — all of which were .xls, and all of which may now be .xlsx after a batch conversion.

In this scenario, the aggregating workbook's pivot tables or data connections reference the old .xls filenames. Every connection needs to be updated to the new .xlsx filename.

The cleanest way to handle a batch rename like this is to write a VBA script that iterates all connections and replaces .xls with .xlsx in each connection string:

Sub UpdateConnectionPaths()
    Dim cn As WorkbookConnection
    Dim oldExt As String, newExt As String
    oldExt = ".xls"
    newExt = ".xlsx"

    For Each cn In ActiveWorkbook.Connections
        If InStr(cn.OLEDBConnection.Connection, oldExt) > 0 Then
            cn.OLEDBConnection.Connection = _
                Replace(cn.OLEDBConnection.Connection, oldExt, newExt)
        End If
    Next cn
    MsgBox "Connection paths updated."
End Sub

Test this on a copy of the workbook first. Not all connections are OLEDBConnection type — if the script errors, check the connection type (cn.Type) and adapt accordingly.

Prevention: What to Check Before Converting

Running LegacyLeaps's free scan before conversion will identify pivot tables with external data sources — so you know which files will need post-conversion attention before you start.

The scan report shows:

• Which workbooks contain pivot tables with external source references
• Which workbooks have data connections to other .xls files
• Named ranges that may not survive the conversion cleanly
• VBA code that references specific file paths or extensions

Knowing this in advance means you can plan the conversion order — convert source files before the aggregating workbooks that depend on them — instead of discovering broken connections after the fact.

Quick Reference: Pivot Table Refresh Failures After XLS to XLSX

Symptom	Cause	Fix
Shows old data, won't update	Stale or corrupt pivot cache	Delete pivot table, recreate from source
Data source path points to .xls file	Source reference not updated on conversion	Change Data Source to current sheet/range
Error on refresh, no data loads	External connection points to moved/renamed file	Update connection string in Data > Queries & Connections
Named range shows #REF!	Named range broken after conversion	Fix named range in Formulas > Name Manager
Aggregating workbook broken after batch convert	Source .xls files renamed to .xlsx	Run VBA to batch-update connection paths

Pivot table issues are solvable. They're annoying, but they follow predictable patterns — and the fixes above cover almost every case you'll encounter after a format migration.

← Back to all posts