Content

• Steps
• Advice

Drop-down boxes in Excel 2007 spreadsheets can speed up the data entry process by providing the user with a list of items to select rather than having to enter information each time. When you insert a drop-down box into a cell in the worksheet, the cell will display an arrow mark. The user will enter the data by clicking the arrow mark and then selecting the desired item. You can set up a drop-down frame in just a few minutes and this will greatly improve the data entry speed.

Steps

1. 

   Open the workbook for which you want to add a drop-down frame.

2. 

   
   
   Creates a list of items to show in the drop-down box. Enter your data in the order that you want them to appear in the list. The items should be entered in a column or row and do not contain any blank cells.
   • To create a list of desired items on a separate spreadsheet, click the tab of the spreadsheet to be imported. Enter and then highlight the data to display in the list. Right-click the highlighted range of cells, then click "Name a Range" from the list that appears. Enter a name for the range in the "Name" box and click "OK". You then have to protect or hide the workbook to prevent other users from changing this list.

3. 

   
   
   Click the cell where you want the drop-down box to be placed.

4. 

   Click the "Data" tab in the Microsoft Excel 2007 ribbon.

5. 

   
   
   Click the "Data Validation" button from within the "Data Tools" group of data tools. The "Data Validation" dialog box will appear.

6. 

   Click the "Settings" tab and select "List" from the "Allow" drop-down list box.

7. 

   Click the convert button at the bottom of the "Source" box. Select the list of items you want to display in the drop-down box.
   • If you created a range of names then in the "Source" field enter an equal sign and the name of the range.

8. 

   Select or uncheck the "Ignore blank" box, depending on whether you allow the cell containing the drop-down box to be blank. Make sure that the "In-cell dropdown" option is checked.

9. 

   Click the "Input Message" tab to display a message box when the cell containing the drop-down box is clicked. You need to make sure that the box "Show input message when cell is selected" is checked, then enter a title in the box "Title:" and the message content in the box " Input message: ".

10. 

    Click the "Error Alert" tab to display an error message if data entered in a cell contains an invalid drop-down box. Make sure that the "Show error alert after invalid data is entered" check box is checked. To display warnings or information but not prevent invalid data entry, select "Warning" or "Information" from within the "Style" drop-down list frame. To display messages and prevent invalid data entry, select "Stop" from the "Style" drop-down list box. Enter a title in the "Title:" field and the error message text you want to display in the "Error message:" field.

11. 

    Click "OK" to save your validation criteria and create a drop-down box. advertisement

Advice

• If the drop-down list item is longer than the containing cell, you can change the cell width to display all the text.
• To delete a frame or drop-down list, click the cell containing the frame / drop-down list. Click the "Data" tab in the ribbon of Microsoft Excel 2007. Click the "Data Validation" button in the "Data Tools" tool group. Click the "Settings" tab, click the "Clear All" button then click "OK."