Add a Dropdown to a Cell

January 18, 2024 - by Bill Jelen

Problem: I need my sales managers to select a product from our company’s product line. All the pricing lookups in the worksheet rely on the product being entered correctly. I find that if I allow my managers to type an entry, they will find too many ways to misspell items. For example, where I may be expecting PDT-960, they are likely to enter PDT 960, 960, and many other variations. If I could offer them a list to select from, they would automatically select the correct spelling of the product.

Strategy: You can easily allow managers to select from a list by using the Data Validation command. It turns out that every cell has a data validation setting to allow any value. You can change this default setting:

1. In an out-of-the-way section of the worksheet, type a valid list of values.
2. Select a cell where the person will be entering data and choose Data, Data Validation.
3. Choose the Allow dropdown and change Any Value to List. The check box for In-Cell Dropdown appears and is automatically checked.
4. Point to the range in the Source field. Alternatively, if the list is short, you can skip step 1 and type the list items, separated by commas, in this box. This particular worksheet already has the valid products as the first column of a lookup table used to get prices.

The data validation dialog box. On the Settings tab, choose to Allow a List. The source is K2:K6. — Figure 1478. Specify the location for the list.

5. Optionally, use the Input Message tab of the Data Validation dialog to provide instructions to the sales managers. You can also use the Error Alert tab to display custom text when the sales managers do not select from your list.

The Data Validation dialog and the Input Message tab. Choose Show Input Message When Cell is Selected. Type a title such as Please Select From The List. Type an input message such as To Make Your Life Easier, we've added a drop down list to this cell. — Figure 1479. Optionally, provide a ToolTip with a note.

6. Click OK to apply the validation.
7. When someone selects the cell, a dropdown will appear, along with your input message. Choose the dropdown arrow, and the managers will be able to select from a list of products.

When you select the cell with the List data validation, a drop-down menu and tool tip appear. Click the drop-down arrow to select from a list. — Figure 1480. Choose from the list.

Additional Details: After you have set up the validation in one cell, you can copy it to other cells. You select the cell and press Ctrl+C to copy. Then you select cells B7:B20 and select Home, Paste dropdown, Paste Special, Validation.

Gotcha: I am always on the lookout for sales managers who know just a little too much about Excel. If a manager were smart enough to delete row 5, he could also delete row 5 of the lookup table off to the right. If you store your list on a hidden sheet with a range name, you can prevent this.

Gotcha: If someone copies a bunch of cells and pastes them over your validated cells in B, the validation will not work. Anyone can get an invalid value in a cell by using Copy and Paste.

This article is an excerpt from Power Excel With MrExcel

Title photo by Nathan Dumlao on Unsplash