Add a Drop Down Box in Excel 2007

Adding a drop-down box to your Excel 2007 spreadsheet can speed up data entry by offering users a list of items to select from instead of having to type information each time. When you put a drop-down box in a spreadsheet cell, the cell displays an arrow. You enter data by clicking the arrow and then selecting the desired entry. You can set up a drop down box in just a few minutes, and greatly improve your data entry speeds.

Steps

  1. Open the spreadsheet in which you want to add the drop-down box(es).
  2. Create a list of the items to appear in the drop-down list. Type the data in the order it will be displayed in the list. The entries should be typed in a single column or row and should not contain any blank cells.
    • To create the list of the desired items on a separate worksheet, click the tab of the worksheet in which to enter the data. Type and then highlight the data to appear in the list. Right-click the highlighted range of cells and click "Name a Range" from the list that appears. Type the name for the range in the "Name" box and click "OK." You can then protect or hide the worksheet to prevent other users from making changes to the list.
  3. Click the cell you want to place the drop-down box in.
  4. Click the "Data" tab of the Microsoft Excel 2007 ribbon.
  5. Click the "Data Validation" button from the "Data Tools" group. The "Data Validation" dialog box appears.
  6. Click the "Settings" tab and then click "List" from the "Allow" drop-down list box.
  7. Click the toggle button at the end of the "Source" box. Select the list of items you want to have in your drop-down box.
    • If you created a range name, in the "Source" box, type an equal sign and then type the name of the range.
  8. Select or clear the "Ignore blank" check box, depending on whether you allow the cell containing the drop-down box to be left blank. Make certain the "In-cell dropdown" check box is selected.
  9. Click the "Input Message" tab to display a message box when the cell of the drop-down box is clicked. Make certain that the "Show input message when cell is selected" check box is selected and then type the "Title:" and "Input message:" that will appear in the message box.
  10. Click the "Error Alert" tab to display an error message if invalid data is entered into the drop-down box cell. Make certain that the "Show error alert after invalid data is entered" check box is selected. To display a warning or information but not prevent invalid data from being entered, select either "Warning" or "Information" from the "Style" drop-down list box. To display a message and prevent invalid data from being entered, select "Stop" from the "Style" drop-down list box. Type the "Title:" and "Error message:" that you want to appear.
  11. Click "OK" to save the validation criteria and create the drop-down box.



Tips

  • To delete a drop-down box, click the cell containing the box. To delete a drop-down list, select the cell with the list. Click the "Data" tab of the Microsoft Excel 2007 ribbon. Click the "Data Validation" button from the "Data Tools" group. Click the "Settings" tab, click the "Clear All" button, and then click "OK."
  • If a drop-down list entry is longer than the cell containing the drop-down list box, change the cell width to reveal the entire text.

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Add_a_Drop_Down_Box_in_Excel_2007&oldid=349395"