60-Second Skills #2: Drop-Down Menus

by Bruce Kirsch on August 31, 2012

Problem: You want to insert a drop-down menu in Excel so a selection chosen in that drop-down can drive certain calculations.

REFM 60-Second Skills Solution: First, in the Excel worksheet in which you wish to insert the drop-down menu, type the items that you wish to be the choices in the drop-down menu in consecutive cells such as here:

Next, select the cell in which you wish the drop-down menu itself to reside.

Drop-down menus are part of Excel’s “Data Validation” functionality. In Excel 2007, go to the Data tab and then select Data Validation.

When the dialog box appears, select “List” from the “Allow” menu.

When you do so, this is what you will see:

Next click on the icon on the right of the “Source” section, and then with your mouse select the range of cells in which you had previously input the choices, and then click the icon in the Source menu again, and then click OK.

In the spreadsheet now you will see that a drop-down arrow has appeared in the cell you selected prior to selecting the Data Validation function.

Now you can reference the cell coordinates where the drop-down menu resides in any formula to drive the results of that formula.

Voila!

Posted in:

Previous post:

Next post: