Find Out How to Add a Dropdown to a Cell - Step-by-Step Instructions in Microsoft Excel
Strategy: You can easily allow the manager 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.
-
Select a cell and choose Data – Validation from the menu, as shown in Fig. 1483.
-
Choose the Allow dropdown and change “Any value” to “List”. The checkbox for In-Cell Dropdown appears and is automatically checked, as shown in Fig. 1484.
-
You can type your list right in the Source field, as shown in Fig. 1485. Or, if you already have the list of products on the worksheet, you can reference the range containing the list. This particular worksheet already has the valid products as the first column of a lookup table used to get prices.
-
Select the Collapse button at the right end of the Source box and highlight the range containing the valid products, as shown in Fig. 1486.
-
Choose OK to close the Validation dialog. As shown in Fig. 1487, each time the cell is selected, a dropdown will appear.
- Choose the dropdown arrow, and the manager will be able to select from a list of products, as shown in Fig. 1488.
Additional Details: After you have set up the validation in one cell, you can copy it to other cells. Select cell B6 and hit Ctrl+C to copy. Select cells B7:B20. Select Edit – Paste Special – Validations.
Gotcha: I am always on the lookout for the sales manager who knows just a little too much about Excel. If the manager were smart enough to delete row 5, he could also delete row 5 of the lookup table off to the right.
Gotcha: In Excel 97, any validation cells that appear above the Freeze Panes area will not work.
Gotcha: If someone copies a bunch of cells from J10:J20 and pastes them over your validated cells in B, the validation will not work. Anyone can get an invalid value in the cell by using copy and paste.
Summary: Use the List option with Data Validation to provide a dropdown in the cell.
Commands Discussed: Data – Validation
Images
