An issue with the Data Model is preventing Microsoft Excel from opening this Workbook. Try restarting Microsoft Excel.

Question

Hello.

Today pivot tables in Microsoft Excel stopped working on my workstation for inexplicable reasons. I'm trying to work with large datasets that need to get imported into SQL server and I use Pivot Tables frequently to check for inconsistencies.

Our Excel-based data templates are saved in the older *.xls file format for compatibility reasons. Today, as I attempted to create a pivot table, I now get an error message:

"An issue with the Data Model is preventing Microsoft Excel from opening this Workbook. Try restarting Microsoft Excel."

Here's a screenshot:

I closed Excel, tried again. Didn't work.

I restarted my workstation. Problem persisted.

The problem occurs on my workstation at work, and I am working remotely from home. My version of Excel at work is:

Microsoft® Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200) 64-bit

My home laptop has the same version of Excel, but the error does not occur. I can create pivot tables.

How do I fix this problem?

Rupertsland,

Canada

Answer 1

Hi Snow Lu.

I just wanted to let you know that I found a solution to my PivotTable problem.

There is a setting under Data in Excel's options that was turned on and caused the error. The option is "Prefer the Excel Data Model when creating PivotTables, QueryTables, and Data Connections." This option must be unchecked when a user is working with Excel files saved in the older *.xls (97-2003) file format. Otherwise, they will see the error in question. I never got the error when working with the newer file formats, which I thought strange. So, that led me to think this was an issue affecting newer features in Excel.

Here's a screenshot:

Answer 2

Could you remove all the sensitive information and share us a file to reproduce your issue?

You can upload to OneDrive, Google Drive or DropBox. Then post a link here.

Answer 3

Hi Snow Lu.

I placed a copy of the file in the following shared OneDrive folder:

Folder

I cannot provide you with any data within the file. You will have to enter a little bit of data to test the pivot table command.

Sincerely,

Rupertsland,

Canada

Answer 4

I just need a file to reproduce your issue. No actual data is needed.

Answer 5

Oh, did you not click on the link to get to the file in the OneDrive folder in my previous reply?

The file is there.