Excel crashes when refreshing pivot from protected .xlsb file

Question

Dear all,

I am currently experiencing the following problem in Excel with pivot tables:

Excel always crashes if I'm refreshing or creating a pivot table which its source data is in an unopened .xlsb file with workbook protection. This happens no matter whether the file containing the source data (the source file) is on a network drive or a local drive.

Excel will NOT crash under any of the following situation,

1. The source file is opened during pivot table refresh/creation; or
2. The source file is in any other file format (e.g. .xlsx); or
3. The source file does not contain workbook protection.

I have tested it on multiple machines and the symptoms are consistent over all the machines.

I am using Microsoft 365 Excel version 2201 (64-bit) on window 10.

Can you kindly advise if anything can be done to this issue? Thank you in advance.

Best,

Hunter

Answer 1

Hi Igor,

The crashing problem only occurs if the source data workbook is structed protected, password encrypted workbooks do NOT cause similar crashing.

I have tried on multiple new workbooks and the symptoms are consistent.

Please find the detail steps of my testing as below,

1. Open a new Excel workbook, input data in A1:B2 in sheet1
2. Save this workbook in .xlsb format (referred to as source.xlsb in following)
3. Setup workbook structure protection with password, then save file again
4. Open another new Excel workbook (refer to pivot.xlsx in following)
5. In pivot.xlsx, click insert pivot table, set data source to A1:B1 in sheet1 in source.xlsb
6. Once the pivot table is created, close source.xlsb
7. Now refresh the pivot table in pivot.xlsx, Excel will crash and end itself without any error message

Please let me know if more information is needed. Thanks.

Best regards,

Hunter

Answer 2

Hi Igor,

Apologies on the late reply.

Please find my update as below,

1. I have tested on another machine under version 2202 (Build 14931.20132), but the crashing problem still persists.
2. I have performed a clean boot and tested again (both with or without opening Excel in safe mode), but it does not help either.
3. Again, there is no error message, the Excel App just ends itself.

Please advise what can be my next step. Thank you very much.

Best regards,

Hunter

Answer 3

Hello Hunter,

May I know if there are any updates for this issue? Please let us know if you managed to update Office or perform a clean boot and refresh the Pivot data.

Best Regards,

Igor

Answer 4

Hi Igor,

Thank you for your reply and suggestion. Please find my updates as below,

• Yes, all machines that I have tested on are under version 2201 (Build 14827.20220)
• This version of Office seems to be the latest under my current license. (For more detail I will need to contact my system administrator)
• I have tried again in safe mode and the symptom persists
• To perform a clean boot I will need to contact my system administrator and may take some days
• There is no error message, the Excel App just ends itself

Thank you for your kind patience and assistance.

Cheers,

Hunter

Answer 5

Hello Hunter,

Sorry for the inconvenience caused by this issue.

We have tried to reproduce the same behavior on our side with Excel version 2202 (Build 14931.20132 Click-to-Run) Current Channel on Windows 10. But we didn’t meet the crashes while creating or refreshing Pivot table with data source from unopened, protected .xlsb file.

To narrow down the root of this issue we kindly ask you to share some additional information:

• As you mentioned you tested this symptom on multiple machines, may I know if all machines have the same version of Excel?
• If it is possible, please update Office to the latest version and try to test again.
• To narrow down the interference of third-party add-ins open Office apps in safe mode.
• You may also perform a clean boot in Windows to see if this behavior is caused by third-party software.
• If you receive any error messages, please post the screenshot.

We know that the initial suggestions do not always solve an issue but we can work together to fix it. Thank you for your patience and understanding.

Best Regards,

Igor