How Do I Create Drop-Down Boxes in Excel?
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.
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.
Sort the values according to the order that you want the information to appear in the drop-down box.
Click the cell in which you want the drop-down box to appear.
Click the "Data" tab and click "Data Validation" in the "Data Tools" group. Click the "Settings" tab.
Click the arrow next to the drop-down box in the "Allow" section and click "List."
Check the "In-cell Dropdown" box.
Check the "Ignore Blank" if you do not want to require the user to select an entry.
Click the arrow after the "Source" field and select the list of values from your Excel spreadsheet. Press "Enter."
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.