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

It's working.

MBP Mid 2014, MacOS Big Sur 11.7.2

Excel for Mac 16.69.1 (Microsoft 365 subscription)

Just opened today (27/jan) my spreadsheet (last opened on 20/jan) and stocks updated as expected. :-)

Answer 2

Hi Steve. I am happy to report that it is now working for me on both of my devices! Hopefully stays that way for a long long time.

Thanks for ushering this through and following up on it. I got nowhere going through tech support, by the way.

Answer 3

Microsoft provides Office for Mac users a huge array of options to catch bugs like this before they reach a general release build. The course of this problem shows the importance of at least some users taking advantage of the options offered by Microsoft.

Years ago, only Microsoft employees, a handful of large organizations, and Mac MVPs were offered opportunities to test pre-release software. Now, anyone with an unrestricted subscription can voluntarily test pre-release builds of Office for Mac.

There is a separate forum exclusively for beta channel users. If you look at that forum you will see that this problem was not discussed in the beta channel. It wasn't until the problem was discovered in the general release builds that it became a topic here in the regular Community forum. 

If a problem doesn't get discovered during the beta releases, that presents a hurdle that is difficult for Microsoft to overcome. Microsoft Excel undergoes constant development. Different teams are working on various features and constructing interface elements. Think about the timing. General release builds are monthly. Beta channel builds are weekly.

The cycle time from discovery to action is hugely different. A new bug in a beta build can be caught in a matter of days and possibly fixed before ever making it to the general release. If the bug makes it to general release, the fastest possible turn around is 2 months if a problem can be fixed instantly. Longer for a complex problem like the data types issue which involved fixing server software.

It's clear to me that Microsoft needs more volunteers from the general community to step up and be beta tester guinea pigs. While beta testing is not for most people, there are organizations that would directly benefit by ensuring mission critical features are not adversely impacted, as the data type failure indicates.

Microsoft lets Mac users have a wide variety of testing options. There is the Current Channel Preview ****option, which lets you get the upcoming current channel a week earlier than everyone else. This would help a small IT department that could have one Mac test mission critical workbooks before releasing the update to everyone in the organization. Maybe a week of testing isn't enough. You could have that one test Mac install the Current Channel Preview and test for a whole month and week before installing the update. There's flexibility here. 

Then there is the Beta Channel. This has weekly builds and lets organizations and brave individuals test new features as well as bug fixes. If some beta channel users had reported the data types problem, it might have never appeared in 16.65 and later builds. But the data types problem wasn't reported in the beta channel and the problem reached Current Channel. 

Right now there are two major new features being tested in the beta channel: Power Query and the Javascript macro recorder. Feedback from beta channel testers is needed for these new features as well as to provide diligence to make sure existing features didn't get broken along the way.

The number of possible user scenarios is nearly endless. That's why it's important for a significant number actual users to be brave enough to try the beta channel builds and provide feedback to Microsoft both in the beta channel forum and using the built-in Help Menu > Feedback ****option, which sends metrics about beta channel builds directly to the development team.

For the reasons above I ask that those who could possibly run a Mac or two on the beta channel to please join the beta program. Here's instructions:

Welcome to the Office Insider for Mac community!

Answer 4

Echoing thanks to Jim for stewarding this issue to a solution.

And Jim, you make a compelling call out to the Beta program. I cannot count the wasted hours I've lost with unhelpful, unknowledgeable, and frankly dangerous MS tech support agents (in their recommendations, I lost 15 years of data I had saved in OneNote — shame on me for trusting OneDrive to be reliable, shame on them for poor advice).

Given my recent experience though with MS tech support, I'd want to know more about the Beta program experience. Am I working with the actual software engineers behind the features? Or just tech support people? If the actual engineers, then sure I'd be interested. If tech support people, then you couldn't pay me enough to sign up... Sorry but no thanks.

Answer 5

It's a blend.

In Current Channel and Current Channel Preview builds receive regular Microsoft Support, which seems to be AI bots with human assist. Generally they receive boiler plate replies which are wrong more than 50% of the time.

Beta Channel build feedback when choosing the I Don't Like Something option is always read by a human, usually someone from the Microsoft Excel development team, but sometimes someone from Microsoft Support. The developers are very keen on wanting direct feedback from beta testers. They want it from the current week's beta builds on new features and things that are broken. Beta channel feedback gets the highest priority. Testers who provide consistently good feedback are noted and their feedback is prioritized.

MVPs have more access to Microsoft developer teams, but we get the same beta builds as the Beta Channel and send feedback the same way. https://mvp.microsoft.com/en-US/pages/what-it-takes-to-be-an-mvp

Usually the beta channel builds are useable but there have been rare instances where the beta channel builds arrive in an unusable state. The presumption is that if you are in the beta program you have enough smarts to know how to restore the previous build if necessary to keep you going.