When you create an input form in Microsoft Excel, understanding how to make the application work for you can save hours of manual input. If you want to narrow question responses to specific options, you can use a drop-down response menu. These instructions are written for Excel 2010, so the steps may vary slightly if you use a different version.
Enter each of the items that you want to appear in the pull-down list on a new worksheet in your Excel workbook. Place one item per cell with each one listed in an adjacent cell. For example, list the items in columns with one option each in cells A1, A2, A3 and so on. You could also list them in a row with one in each cell, such as A1, B1 and C1, until all your data is listed. The drop-down will be ordered based on the entries, so organize the items accordingly.
Highlight all of the cells in the list. Right-click your mouse and choose "Define Name" from the menu. Create a name for the list and enter it in the "Name" field. Do not use spaces in the name. Click "OK." This name is only used to select the list for the drop-down; it does not appear for users.
Click the cell where you want to create the drop-down menu. Select the "Data Tools" section by clicking "Data." Choose "Data Validation" and click "Settings." Select "List" in the Allow field.
Click the "Source Name" input field and type a "=" followed immediately by the name you assigned to the list on the previous worksheet. For example, if your list was named "options," type "=options" in the source name field.
Click the check box beside "in-cell dropdown" so that the drop menu appears in the cell. If you want to require that responders choose an option, clear the "Ignore blank" check box.
Click "Input Message" to set a message to users when the cell is clicked. This is a good place to explain what selection you want the user to make or other specifics. Leave it blank if you do not want to use this feature.
Navigate to the "Error Alert" tab. Click the check box beside the option to show an error notice if invalid data is entered. Use the provided text fields to set the contents of the message. Clear the check box if you do not want the message to appear.