New behavior with Data Validation Drop Down lists in Insider Edition!

jdellasala

Well-known Member
Joined
Dec 11, 2020
Messages
751
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. 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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Forum statistics

Threads
1,216,503
Messages
6,131,020
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top