I’m working on a spreadsheet that needs a drop down list, however once created, the drop down arrow acts like it is disabled and does nothing. Google search indicates that this has been happening for some people over the years, and the people running the microsoft help forums appear to be clueless about it. Anyone else experiencing this or know of a solution?
I’ve used these all the time without any issues so I created a test spreadsheet and took the screenshots below. Then I went back to Excel and dang if the drop down stopped working!
However, a bunch of other things also were behaving badly and when I tried to close Excel I got an error about something going wrong. When I reopened everything was working again. I haven’t been able to make it fail a second time.
Does it never work for you or is it just intermittent?
What version of Excel are you using? (I’m currently running Office365 (shows up as Excel version 16.40 (20081201) on Catalina)
1 Like
I tested this by duplicating your spreadsheet. All seems to be working well. I opened, tested, and closed the spreadsheet multiple times. My version of Excel is 16.40 (20081000) with no updates available (Office365 subscription).
1 Like
First time I have tried to use this on a Mac. I initially set up a named range, but then just tried it on hard coded cells like your example above, nothing worked. I have read that some think it helps to reinstall, others find this doesn’t help, and I’ve seen reports of it dated 2008 as well as this year and years in between. Will try again and report back…
I tried an entirely new spreadsheet and the dropdown worked. It still didn’t work in the original spreadsheet which was also newly created today, by copying in from Numbers (with apologies, I just know Excel better).
Seems the problem spreadsheet was permanently poisoned somehow. When I copied from it to the new spreadsheet, the dropdown still worked.
Interestingly, when you click the down arrow on the drop down, the red amber and green jewels on the window go grey. In the bad spreadsheet this still happened but the drop down did not drop down.
I’ve been unable to make it fail a second time. I also set up a drop down using a named range (my preferred method) and no troubles there, either.
Also tried creating one in Numbers and exporting to Excel. That worked fine, too.
I hate bugs like these. Intermittent ones are the worst and probably explains why you’ve seen complaints about it going back so far. Unless they get a critical mass of bug reports this sort of thing is going to be way down their list of things to fix.
(Unless they use a dropdown in Excel in their bug list and it breaks. 
)
1 Like
Office for Mac is really poor. I work in a Windows office but I’m one of the few using a Mac and I’m shocked as to how buggy and feature poor both Excel and PowerPoint are compared to the Windows versions.
3 Likes
I wonder if here’s another method - rather than using the drop down arrow - that could be used to activate the list. (And might then be automated.)
(I’ve never used this feature of Excel, by the way. If it can’t be made to work reliably I probably won’t bother.)
Microsoft probably wants you to use Windows. 
It makes for a much more compact spreadsheet if you are doing a trade study where you want to what-if different combinations of equipment to get the resulting total (weight, cost, whatever). It worked for me on a brand new spreadsheet.
1 Like
I will say that mostly I don’t know in advance what the acceptable values would be. I’m mostly dealing with names of things that vary from client to client. For example, mainframe system IDs.
Still, it could be a useful (though cumbersome) thing to set up. So now it’s in my kitbag, thanks! (And “cumbersome” might mean “automate it”.)
What features are you missing? I haven’t run into any since getting to Office365 on Mac, nor have I found it buggy. Other than this one, apparently.
Might not be enough of a power user, but I’ve been able to do everything Excel related I’ve needed to (pivot tables, array math (when I can wrap my head around it), etc.)
Excel
- Ability to write VBA
- If I am copying data from one row to another row, it always seems to hang for like a second, so you can’t do CMD+C – down – CMD+C – down etc. on anything bigger than tiny spreadsheets (no idea how to resolve this - turning calculation fixes it but why should I?!)
- Not a bug, but sharing an office with Windows users means that external links don’t work
- Creating charts is an awful experience in general
- I can’t connect to a SQL Server database from an Excel created using Windows (the Excel works on Windows but not Mac)
PowerPoint
- Ability to use a template by default
- Ability to use my company’s PowerPoint add-in ribbon (there’s a known issue which means this doesn’t work apparently)
Outlook
- Often calendar invites are sent without any text (whatever I type is lost when I send an invite)
- I can’t access and search the corporate address book like I can on Windows
- I can’t add VBA rules (e.g. to automatically delay send an email if it is outside of business hours)
- I can’t apply a category to multiple calendar events
Just a few off the top of my head - there are more I’m sure.
On creating charts: I’ve taken to building a couple of Keyboard Maestro scripts to create my two favourite chart types. Also to set fonts for eg titles and legends.
1 Like
Ah, that makes sense.
I bet the VBA engine is buried in Windows somewhere (or has dependencies there).
I have noticed the copy/paste delay although it seems to be better lately (last month or so).
And since my work locks things down so well I can’t use the native Outlook client with my work email so I don’t notice any problems there. The OWA has limitations that annoy me…
@MartinPacker: Keyboard Maestro rules…
1 Like