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

Okay thank you Snow Lu.

I just forwarded this info on to our IT staff.

Ruperstland,

Canada

Answer 3

Thank you for telling us the solution and glad you to hear that you had resolved it.

I will deliver it to anyone who meet same issue. Thank you again:)

Answer 4

Getting same error after a fresh install of 365. There is some problem with the data model. I can import a small column of data as a query, but I am not able to save it in the data model.

Answer 5

Have you tried the steps disable Prefer the Excel Data Model when creating PivotTables, QueryTables, and Data Connections.

Are you using xls file format?