How Do I Create Drop-Down Boxes in Excel?

By Angela M. Wheeland

When you're creating an Excel spreadsheet that collects data, you might want to insert a drop-down box. This is useful in focusing response options, as it contains a list of predetermined values, restricting the user from entering any information other than the values included in the list. To create a drop-down box, you must create a list of values and insert the values using Excel's Data Validation.

Step 1

Create a list of values that you want included in your drop-down box. It cannot contain any blank cells and must be created in a single column or row.

Step 2

Sort the values according to the order that you want the information to appear in the drop-down box.

Step 3

Click the cell in which you want the drop-down box to appear.

Step 4

Click the "Data" tab and click "Data Validation" in the "Data Tools" group. Click the "Settings" tab.

Step 5

Click the arrow next to the drop-down box in the "Allow" section and click "List."

Step 6

Check the "In-cell Dropdown" box.

Step 7

Check the "Ignore Blank" if you do not want to require the user to select an entry.

Step 8

Click the arrow after the "Source" field and select the list of values from your Excel spreadsheet. Press "Enter."

Step 9

Click the "OK" button and click the arrow next to your drop-down box to verify the data.

Tips & Warnings

  • If you want Excel to display an error message if invalid data is entered in the drop-down box, click the "Error Alter" tab from the "Data Validation" dialog box. Choose a warning method and enter a warning message.
  • To delete a drop-down box, select the drop-down box and click the "Data" tab. Click "Data Validation" and click the "Settings" tab. Click "Clear All," and the drop-down box will be deleted from your spreadsheet.
  • Hide the values of your drop-down box by highlighting the list, clicking the "Home" tab and clicking the "Format" drop-down box from the "Cells" group. Click "Hide & Unhide" and click "Hide Rows." Hiding rows also hides other data included in the rows.