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.

Download Workbook

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.

How to use pick from drop-down list feature in Excel

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.

How to use pick from drop-down list feature in Excel 02

 

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.

How to use pick from drop-down list feature in Excel 03

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.

How to use pick from drop-down list feature in Excel 04

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.

  1. Once the macro is ready, click Developer > Macros icon in the Ribbon.
  2. Select your macro.
  3. Click Options
  4. 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.
  5. Once you are satisfied with the shortcut, click OK to save.
    How to use pick from drop-down list feature in Excel 05