jdellasala
Well-known Member
- Joined
- Dec 11, 2020
- Messages
- 751
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
The latest version of the Insider Edition of Excel (Version 2202 Build 16.0.14907.20000 64-bit) has a new behavior for Drop Down lists that I suspect people will LOVE! One can now start typing in the cell with the List, and a Tool Tip style entry from the list that matches what is typed will appear and can be selected. This makes long lists much more useable without having to resort to using a formula (typically FILTER) to do this!
When it first appeared for me on Thursday (1/13), I thought it was a bug and reported it, but I since realized it's a feature enhancement.
Some notes about it.
First, if you have editing in cells disabled (Options -> Advanced -> Editing options - Allow editing directly in cells) disabled / unchecked, the selection list will appear under the left side of the Formula Bar instead of under the cell. Enabling it makes it work as expected (unless or until MS fixes it!).
Second, and this was true already, if the list is of Rich Data Types like Stocks, the "hamburger" icon that appears to the right of the item obscures the drop down arrow (which actually is kind of unnecessary now!). In the past the work around I used was to merge the cell with the drop down with the cell to the right of it (and then left justify the merged cells).
Finally, as pointed out in his video of the new feature, Excel Campus - Jon -
notes that if you have a column of drop down cells, once you've selected an item from the tool tip list, you hit [Enter], but the drop down cell remains selected. Hitting [Enter] again will move the active cell down one. I have that feature disabled so would never have noticed it. He also points out that the match is a full word match, so that a search for "root" will not display the item "Arrowroot" in the list.
This makes drop down lists SO MUCH more versatile. If you have the occasion to use Rich Data Types, I highly recommend using them for the list. The cell with the drop down can be referenced for data held within the item. For example if you point the list to a column of Stocks Data Types in cell A1, you can extract the CURRENT (delayed) price using the formula "=A1.Price". Great for an interactive dashboard, especially if you have custom Data Types you've created with Power Query.
Jerry
When it first appeared for me on Thursday (1/13), I thought it was a bug and reported it, but I since realized it's a feature enhancement.
Some notes about it.
First, if you have editing in cells disabled (Options -> Advanced -> Editing options - Allow editing directly in cells) disabled / unchecked, the selection list will appear under the left side of the Formula Bar instead of under the cell. Enabling it makes it work as expected (unless or until MS fixes it!).
Second, and this was true already, if the list is of Rich Data Types like Stocks, the "hamburger" icon that appears to the right of the item obscures the drop down arrow (which actually is kind of unnecessary now!). In the past the work around I used was to merge the cell with the drop down with the cell to the right of it (and then left justify the merged cells).
Finally, as pointed out in his video of the new feature, Excel Campus - Jon -
This makes drop down lists SO MUCH more versatile. If you have the occasion to use Rich Data Types, I highly recommend using them for the list. The cell with the drop down can be referenced for data held within the item. For example if you point the list to a column of Stocks Data Types in cell A1, you can extract the CURRENT (delayed) price using the formula "=A1.Price". Great for an interactive dashboard, especially if you have custom Data Types you've created with Power Query.
Jerry
