Dropdown lists are a common example of data validation used in organizing data input. In this article, we are going to show you how to use pick from drop-down list feature in Excel.
The "Pick from drop-down list" feature generates a drop-down list on the fly, based on values on the adjacent cells in the same column. In the following example, we see a drop-down with values in the column C. The best part about this feature is that Excel can recognize the title and exclude it from the list selection.
Auto-complete works here, but it does not offer a solution if there are items starting with same characters, such as Columbia and CBS Records.
Context menu
You can see the feature’s name in the context menu when you right click a cell. Remember that at least one adjacent cell should be filled either up or down direction to use this feature.
Alt + Down Arrow Shortcut
This shortcut is an easy way to display the drop-down list. When you're on the cell you want to fill, press the Down Arrow (↓) key while holding Alt or Alt Gr keys.
Menu Key + K Shortcut
Second shortcut option depends on whether you have the context (right-click) menu open.
The menu key (the application key) is between the Windows (or FN) and CTRL keys on the right-side on most keyboards. This key opens the right-click menu for the selected item.
Furthermore, the “k” letter is underlined and this means that you can use this shortcut key while the menu is active.
Using VBA to populate pick from drop-down list
You can call the same functionality using VBA as well. With the macro method, you can assign whatever shortcut you'd like to open this feature.
Sub OpenList()
CommandBars("Cell").Controls("Pic&k From Drop-down List...").Execute
End Sub
Below are the steps for adding a shortcut to your macro.
- Once the macro is ready, click Developer > Macros icon in the Ribbon.
- Select your macro.
- Click Options
- Press the key you would like to use as shortcut on your keyboard. If the shortcut is already taken, you can also add Ctrl + Shift to assign it.
- Once you are satisfied with the shortcut, click OK to save.
