Adding drop-down boxes to an Excel 2007 spreadsheet can speed up data entry by providing a list of options instead of having to type them out one by one. When you place a drop-down box on a worksheet cell, an arrow appears on the cell. Data can be entered by clicking the arrow and then selecting the desired entry. You can install a drop-box in just a few minutes and significantly increase data entry speed.
Step
Step 1. Open the spreadsheet to insert a drop-down box in it
Step 2. List the entries you want to appear in the drop-down
Type the data in the order you want it to be displayed. Entries must be entered in a single column or row and must not contain blank cells.
To list the desired data on a separate worksheet, click the tab of the worksheet where you entered the data. Type and then select the data you want to appear on the list. Right-click the selected range and then click "Name a Range" from the list that appears. Type a range name in the "Name" box and then click "OK." You can protect or hide the worksheet to prevent its contents from being changed by other users
Step 3. Click the cell where you want to place the drop-down box
Step 4. Click the "Data" tab on the Microsoft Excel 2007 ribbon
Step 5. Click the "Data Validation" button in the "Data Tools" group
The "Data Validation" dialog box will appear.
Step 6. Click the "Settings" tab and then click "List" from the list down the "Allow" box
Step 7. Click the button at the end of the "Source" box
Select the list you want to enter in the drop-down box.
If you have already created a range name, type an equal sign followed by the range name in the "Source" box
Step 8. Select or clear the "Ignore blank" checkbox, depending on whether you allow the cells containing the drop-down box to be left blank
Make sure the "In-cell dropdown" checkbox is selected.
Step 9. Click the "Input Message" tab to display a message box when the drop-down cell is selected
Make sure the "Show input message when cell is selected" checkbox is selected and type the message you want to appear in the "Title:" and "Input message:" boxes.
Step 10. Click the "Error Alert" tab to display an error message if the data entered in the drop-down box does not match
Make sure the "Show error alert after invalid data is entered" checkbox is selected. To display a warning or information but allow incorrect data input, select "Warning" or "Information" from the "Style" drop-down box. To display the message and prevent erroneous data from entering, select "Stop" from the "Style" drop-down box. Type the message you want to display in the "Title:" and "Error message:" boxes.
Step 11. Click "OK" to save the validation criteria and create a drop-down box
Tips
- To remove the drop-down box, click the cell that contains the box. To delete the drop-down list, select the cell that contains the list. Click the "Data" tab on the Microsoft Excel 2007 ribbon. Click the "Data Validation" button from the "Data Tools" group. Click the "Settings" tab then click the "Clear All" button then click "OK."
- If the entry in the drop-down list is wider than the column of cells, change the width of the column of cells to display the entire text.
