Pivot Table Issues

How to Fix Excel Crashing When Refreshing a PivotTable from a Protected XLSB File

Adam DavisAdam Davis Nov 3, 2026 869 views

Question details

Microsoft Excel closes unexpectedly when a user attempts to refresh a PivotTable that relies on a closed, structure-protected XLSB file as its external data source.

Fix Excel Crashing When Refreshing PivotTables from Protected XLSB Files
Product
Microsoft Excel
Device & OS
not provided
Scenario
Refreshing a PivotTable connected to an external binary workbook (.xlsb) that has workbook structure protection enabled.
Observed behavior
Excel crashes completely without displaying any error messages, even when running in Safe Mode or after a clean boot.
Before you start

Ensure you save all other open workbooks before attempting to refresh the PivotTable, as the silent crash will cause unsaved data in all active Excel windows to be lost.

Solution 1Recommended

Keep the Source XLSB Workbook Open

The simplest and most immediate workaround is to ensure the source XLSB file is open before refreshing the PivotTable, which prevents the application defect from triggering.

This crash is a known defect in certain versions of Microsoft 365 (such as Version 2202) where Excel fails to properly read the data cache from a closed binary file with structure protection. Keeping the file open bypasses the background connection failure.

1
Open the source file

Locate and open the structure-protected XLSB source file in Excel.

2
Switch to your PivotTable

Leave the source file open and switch to the workbook containing your PivotTable.

3
Refresh the data

Right-click anywhere inside the PivotTable and select 'Refresh' from the context menu.

Keep the Source XLSB Workbook Open
Successful Refresh: The PivotTable should now update successfully without causing Excel to close unexpectedly.
Free Microsoft Office alternative

Try WPS Office for Stable Data Analysis and PivotTables

Since this crash is an underlying application defect in Microsoft Excel involving XLSB files, switching to a highly compatible and stable alternative like WPS Office can help you manage your spreadsheets and pivot tables without unexpected interruptions.

  1. 1. Download and Install WPS Office: Visit the official WPS website to download the free WPS Office suite and install it on your computer.
  2. 2. Open your XLSB files safely: Launch WPS Spreadsheets and open your structure-protected XLSB source files without worrying about compatibility bugs.
  3. 3. Manage PivotTables smoothly: Create, link, and refresh PivotTables effortlessly using WPS Office's stable data management engine.
Seamless format compatibility with Microsoft Excel (.xlsx, .xls, and .xlsb files).Robust PivotTable support for complex external data analysis without random crashes.Lightweight installation with fast performance, even on older hardware.Free to use with a familiar, easy-to-navigate tabbed interface.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel crash without an error message when refreshing my PivotTable?

This is a known application defect in certain builds of Microsoft 365. Excel fails to process the external data connection properly when the linked binary (.xlsb) file is closed and has workbook structure protection enabled, resulting in a silent application crash.

Does password encrypting the file cause the same crash?

No, password encrypting the file (requiring a password to open it) does not trigger this specific crash. The issue is strictly isolated to workbooks that use the 'Protect Workbook' feature to lock the workbook's structure.

Will updating Microsoft Office fix this issue?

Updating to the latest Microsoft 365 build may resolve the issue, as Microsoft frequently releases patches for known application defects. You can check for updates by going to File > Account > Update Options > Update Now.

How do I change my PivotTable data source after saving my file as XLSX?

Select any cell in your PivotTable, navigate to the 'PivotTable Analyze' tab on the ribbon, click 'Change Data Source', and then browse your computer to select the newly saved .xlsx file.