Adding a drop-down menu to your Excel 2007 spreadsheet can speed up data entry and give users a list of items to choose from, instead of having to type in information time and time again. When you add a drop-down menu to a spreadsheet cell, the cell will show a down arrow. You can then enter your data by clicking on the arrow and selecting the desired item. You can set up a drop-down menu in minutes, and greatly improve data entry speeds.
Steps
Step 1. Open the spreadsheet where you want to add the drop-down menu
Step 2. Create a list of items to be displayed in the drop-down menu
Type the data in the order it will appear in the list. Entries must be placed in a single column or row, and must not contain empty cells.
To create the list of desired objects in a separate spreadsheet, click on the spreadsheet tab to enter the data. Enter and highlight the data to appear in the list. Right-click on the highlighted range of cells, then click on "Define Name" from the list that appears. Type the name for the range in the "Name" box and click "OK". You can now protect or hide the spreadsheet to prevent other users from making changes to the list
Step 3. Click on the cell where you want to insert the drop-down menu
Step 4. Click on the "Data" tab on the Microsoft Excel 2007 toolbar
Step 5. Click on the "Data Validation" button from the "Data Tools" group
The "Validate Data" dialog box will appear.
Step 6. Click on the "Settings" tab and then on "List" from the "Allow" section of the drop-down menu
Step 7. Click the check button at the end of the "Source" field
Select the list of items you want to appear in your drop-down menu.
If you have defined a name for the range, type an equal sign and then the name of the range in the "Source" box
Step 8. Check or uncheck the "Ignore blank cells" box, depending on whether you want the cell containing the drop-down menu to be left blank
Make sure the "List in Cell" box is checked.
Step 9. Click on the "Input Message" tab to pop up a message box when clicking on the drop-down menu cell
Make sure the "Show input message when cell is selected" box is checked, then enter the "Title" and "Input message" that will appear in the message window.
Step 10. Click on the "Error Message" tab to pop up an error message if invalid data is entered in the drop-down cell
Make sure the "Show error message when entered data is invalid" box is checked. To bring up a warning or information, but not prevent invalid data from being entered, select "Warning" or "Information" from the "Style" drop-down menu. To display a message and prevent invalid data from being entered instead, select "Break" from the "Style" drop-down menu. Type in the "Title" and "Error Message" you wish to appear.
Step 11. Click "OK" to save the validation criteria, and create the drop-down menu
Advice
- To remove a drop-down menu, click on the cell that contains the menu. To delete a drop-down list, select the cell with the list. Click on the "Data" tab of the Microsoft Excel 2007 toolbar and then on the "Data Validation" button in the "Data Tools" group. Click on the "Settings" tab and then on "Clear all", then click "OK".
- If an entry in the drop-down list is longer than the cell containing the drop-down menu, change the width of the cell to reveal the full text.
