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

Thank you the work around., much appreciated. I followed the links you posted and installed Office 16.64 and my data is functioning correctly.

I do hope Microsoft addresses this problem as it is sad that we Mac users have to go back to a version from April 13, 2021.

I have shut off auto updater for Microsoft and will monitor here to see if it EVER gets fixed.

Answer 2

It's suggested that we give feedback to Microsoft. When I clicked on help and something I don't like it told me I had to install the latest update before I could leave feedback. Not after I finally got it to work on 16.64 am I going to do that.

Answer 3

Good point!

Send the feedback BEFORE you revert to 16.64

Under ordinary circumstances Microsoft needs feedback on the current update, not previous updates, which is why they ask that you install the current update before submitting feedback. However, in this instance you won't be able to submit after you have reverted.

Answer 4

I've had this issue since early November. Like others, it continues to work fine on our Intel MacBook, but not on our Intel iMac. Both are on the latest OS and version of Excel.

I actually had a successful call with MS support that resolved the issue, but it returns every time I quit excel. Here are the steps I follow to restore that has worked every time:

• Log out of Office 365
  • File > New From Template
  • Click on user circle and sign out.
• Quit all office apps
• In finder, Select Go > Go To Folder and enter this:

~/Library/Containers/com.microsoft.Excel/Data/Library/Preferences

• Delete the two Excel plist files:
  • com.microsoft.Excel.plist
  • com.microsoft.Excel.securebookmarks.plist
• Run installer to remove Office license
  • https://support.microsoft.com/en-us/office/how-to-remove-office-license-files-on-a-mac-b032c0f6-a431-4dad-83a9-6b727c03b193
• Delete Excel from Applications folder & reinstall version 16.59
  • https://learn.microsoft.com/en-us/officeupdates/update-history-office-for-mac
• Open Excel
• Sign into Office 365
• Quit Excel
• Open Excel
  • At this point, the Stock data type is not available under the Data tab
  • Edit a cell and it should ask to “Activate”
  • Enter Office 365 password
• Quit Excel
• Open Excel and it should work at this point

Note, I’m able to run the auto installer up to the point that it says “App is open”.  Quitting Excel and letting it finish installation does not disable the stock data type when I restart Excel.

Everything works fine for several days, but as soon as I quit Excel and reopen, stock data types, along with 365 specific functions like XLOOKUP, stop working. Running the steps above reliably resolves it.

Answer 5

Is it possible you have the auto-updater enabled and in automatic? It would install the newest Excel (currently 16.68) that would activate on the next time you start Excel.