Excel Data Types Not Working

Question

Updated 12/19/2022

Data Types are currently not working as expected for all users of Microsoft Excel. The work-around I recommend is to revert to version 16.64.

If you are affected and haven't already done so, please take a moment to notify Microsoft that you are affected by this problem. The way to do it is to use the Menu Bar and choose Help > Feedback > I Don't Like Something. Microsoft uses the Squeaky Wheel method to prioritize bug fixes. The more reports, the higher up the priority list the bug gets. Please do this from the current update of Excel before reverting to 16.64.

To revert to version 16.64:

Use the Menu Bar and choose Help > Check for Updates to launch AutoUpdate

In AutoUpdate, set updates to Manual to prevent AutoUpdate from automatically updating after you revert to the older build.

In Finder, navigate to the Applications folder

Right-click on Microsoft Excel.app and choose Move to Trash

Restart your Mac

Empty the Mac OS Trash

Use this installer to install build 16.64 of Microsoft Excel: Excel

Some users may be able to find a work-around, which is why I am posting this discussion. Some users will be blocked from this due to licensing or IT department restrictions.

There are third-party solutions available. I don't know which ones are great and which ones are less good. I'm hoping people will try them out and reply here with their results and recommendations.

On the Insert tab of the Ribbon choose Add-ins > Get Add-ins.

In the resulting dialog enter the word Stock in the Search field then click the spyglass icon. There are a lot of add-ins available. Let's chat about them in this thread.

Answer 1

I just tried to update Excel to 16.68 again given that it seemed to work for you. However, the same issue persists for me just as others are also reporting above: Data types work on the first open of the Excel app, but once you quit and re-open Excel, the error re-appears and data types no longer work. My test was simple: first open Excel, type MSFT into a cell, select Stock data type, and test it with <cell>.Price in another cell. That works on the first open of Excel 16.68; but once you quit and re-open Excel 16.68, data types no longer work and it generates this error:

Looking at the release cadence, hopefully next week we'll get a fix presuming the development team has received enough feedback and prioritized this issue. My M365 subscription is up for renewal is Feb-2023... Microsoft ought to give us all a $$ credit for the time lost in not having this feature work, for the time lost providing all of this intel on the issue for free, and for the data losses that have occurred as a result of the incorrect solutions proposed by Microsoft support — in all the re-installing, removing all licenses, re-activating of Office they made me do, I totally lost all of my OneNote notebook that was saved on Onedrive. So be careful, Onedrive can arbitrarily delete data! I never deleted my notebook or it would show up in my Recycle bin. Something went awry in the re-activation process...

Answer 2

Just wanted to mention again, I'm on our MacBook Air now running MacOS 13.1 and Excel 16.68. Stock data types work fine on this computer, I just quit and reopened to test.

The iMac does not work even though it's on the same account, OS and Excel version.

Answer 3

I just tried to update Excel to 16.68 again given that it seemed to work for you. However, the same issue persists for me just as others are also reporting above: Data types work on the first open of the Excel app, but once you quit and re-open Excel, the error re-appears and data types no longer work. My test was simple: first open Excel, type MSFT into a cell, select Stock data type, and test it with <cell>.Price in another cell. That works on the first open of Excel 16.68; but once you quit and re-open Excel 16.68, data types no longer work and it generates this error:

Looking at the release cadence, hopefully next week we'll get a fix presuming the development team has received enough feedback and prioritized this issue. My M365 subscription is up for renewal is Feb-2023... Microsoft ought to give us all a $$ credit for the time lost in not having this feature work, for the time lost providing all of this intel on the issue for free, and for the data losses that have occurred as a result of the incorrect solutions proposed by Microsoft support — in all the re-installing, removing all licenses, re-activating of Office they made me do, I totally lost all of my OneNote notebook that was saved on Onedrive. So be careful, Onedrive can arbitrarily delete data! I never deleted my notebook or it would show up in my Recycle bin. Something went awry in the re-activation process...

Almost exactly what happened to me. Fixed once, data types working, quit Excel, reopened, broken again. Leads me to believe that some configuration file is saved on closing the program and this file is corrupted.

Answer 4

Curiously when I deleted Excel 16.68 and installed Excel 16.64, I faced the opposite experience. On the first open of Excel 16.64, the data types did not work. When I quit and re-opened Excel 16.64, the data types then began working, and seemed to work on the next few attempts to quit and re-open the app. Going back to Excel 16.61 is when data types reliably works for me regardless of how many times I open the app.

Answer 5

I first reported this problem back in May 2022 (I am a Microsoft 365 subscriber). Contacted MS support, and after the usual time-consuming steps that made no progress, eventually got escalated to Tier 3 support. Then spent MANY hours working with Tier 3 technical staff over several months to try to solve it, countless uninstalls, reinstalls, download of third party monitoring tools, etc. The claim was that it was a unique problem to me, that others were not seeing it. This thread makes it clear I am not alone. We finally gave up in September, managed to get the function working after a complete wipe down of both Macs having the problem, reinstall of the OS and latest 365 package. Case was closed. Worked properly for a few months until about a month ago. I replied on the contact I had for Tier 3 support, we shall see it that goes anywhere...

I am convinced there is a bug in Office software, perhaps just Excel. Some file gets corrupted or erroneously saved at some point - could be upon saving of a file, or exiting or restarting Excel, or during a software upgrade, unclear. But it can appear suddenly, even without a software update, on a version where it works fine, then it doesn't the next time you start up Excel on the same version.

I am not willing to go down the 3rd party solution. I am paying MS for this to work. I am getting very tired of doing the total removal of Office and reinstalling, whether it is the latest version of going back. I have seen this on too many versions to make that viable for me.

Come on, Microsoft. Surely you can figure out what is happening and fix it.