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

I can create povit table without any issue. It looks issue on your office application instead of file. You may try repair your office.

Is there anyone can open same file? Does this issue occur on another computer?

Answer 3

I'm still trying to get staff to help test my file.

Strange that it only affects *.xls files.

I will also contact our IT staff to see if they can do a repair.

From the many postings on the internet, this random, unexpected problem of Pivot Tables no longer working in Excel has persisted for more than 5 years across several versions of Excel. Nothing else happened on my workstation during the past week or so, except for Windows and 365 updates. Which leads to me to suspect whether the last Excel update messed up something. But if that were the case, millions of users would be affected.

Although I cannot prove it, there is the possibility of bugs creeping into Excel through updates that end up crippling the pivot table feature under certain conditions. At this point, the user is faced with having to completely uninstall and then reinstall the software. Rather drastic... and a loss of productivity.

I hope the Microsoft 365 team can look into this problem and finally figure out why and fix this problem that's affecting many users and for no known reason. How about a button to "Repair pivot table and data models".

The only other explanation I can think of is file corruption, however that happens. Hence the need to repair or reinstall the software.

Answer 4

Hi Snow Lu.

Sorry for not responding sooner. I was sick last week.

Please know that our company subscribes to Microsoft 365 E3.

On the advice of our IT staff, we uninstalled all the 365 desktop apps on my computer, rebooted, and then reinstalled all the Office desktop apps.

The problem persisted. Same error message.

What do I do now? Do I have to reinstall the whole Windows 10 OS from scratch to wipe out the problem, and start over? Or, is there a way to find the problem and fix it? If the Excel data model is the problem, how do I fix that?

Ruperstland,

Canada

Answer 5

Check from this thread, it seems to be a bug.

"An issue with the Data Model is preventing Microsoft Excel from - Microsoft Community

You may raise a ticket to Micrsoft.

Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. That support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

Contact Us - Microsoft Support