Share via

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

Anonymous
2023-05-08T19:48:19+00:00

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

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2023-05-17T14:56:22+00:00

    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:

    20 people found this answer helpful.
    0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-23T14:56:11+00:00

    Hi Marcde Jong.

    Please see Snow Lu's reply to your message.

    Also, please look at the screenshot above that I posted earlier. It shows my solution.

    I also reinstalled 365 from scratch and the problem persisted... until I unchecked the option Prefer the Excel Data Model when creating PivotTables, QueryTables, and Data Connections. This option MUST BE TURNED OFF if you are working with Excel workbook files saved in the older *.xls (97-2003) file format.

    0 comments