How to Add a Drop-Down Box in Excel 2007

The drop-down box in Excel 2007 spreadsheets can speed up the data entry process by providing users with a list of items to choose from instead of having to enter information each time. When you insert a drop-down box into a cell in a spreadsheet, the cell displays an arrow mark. The user will enter data by clicking on the arrow and then selecting the desired item. You can set up a drop-down box in just a few minutes and this will greatly improve data entry speed.

Table of Contents

Steps

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Add-a-Drop-Down-Box-in-Excel-2007-Step-1-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-1-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 6/6e/Add-a-Drop-Down-Box-in-Excel-2007-Step-1-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-1-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Open the spreadsheet to which you want to add the drop-down box.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/b/bf/Add-a-Drop-Down-Box-in-Excel-2007-Step-2-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-2-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ b/bf/Add-a-Drop-Down-Box-in-Excel-2007-Step-2-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-2-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Create a list of items to display in a drop-down box. Enter the data in the order in which you want them to appear in the list. Items need to be entered in a column or row and should not contain any blank cells.

To create a list of desired items on a separate worksheet, click the tab of the spreadsheet to import data. Enter then highlight the data that will show up in the list. Right-click the highlighted range of cells and then click “Name a Range” from the list that appears. Enter a name for the range in the “Name” box and then click “OK”. You then have to protect or hide the workbook to prevent other users from changing this list.

READ More: How to Open XML Files

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/0d/Add-a-Drop-Down-Box-in-Excel-2007-Step-3-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-3-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 0/0d/Add-a-Drop-Down-Box-in-Excel-2007-Step-3-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-3-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

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

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/83/Add-a-Drop-Down-Box-in-Excel-2007-Step-4-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-4-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 8/83/Add-a-Drop-Down-Box-in-Excel-2007-Step-4-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-4-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click the “Data” tab in the Microsoft Excel 2007 ribbon.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/05/Add-a-Drop-Down-Box-in-Excel-2007-Step-5-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-5-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 0/05/Add-a-Drop-Down-Box-in-Excel-2007-Step-5-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-5-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click the “Data Validation” button from within the “Data Tops” data tools group. The “Data Validation” dialog box will appear.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/16/Add-a-Drop-Down-Box-in-Excel-2007-Step-6-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-6-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 1/16/Add-a-Drop-Down-Box-in-Excel-2007-Step-6-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-6-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click the “Settings” tab and select “List” from the “Allow” drop-down list pane.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/56/Add-a-Drop-Down-Box-in-Excel-2007-Step-7-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-7-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 5/56/Add-a-Drop-Down-Box-in-Excel-2007-Step-7-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-7-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click the toggle button at the bottom of the “Source” pane. Select the list of items that you want to display in the drop-down box.

If you’re creating a name range, in the “Source” box, type an equals sign and the name of the range.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/6/6e/Add-a-Drop-Down-Box-in-Excel-2007-Step-8-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-8-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 6/6e/Add-a-Drop-Down-Box-in-Excel-2007-Step-8-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-8-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Check or uncheck the “Ignore blank” box, depending on whether you allow the cell containing the drop-down to be left blank. Make sure the “In-cell dropdown” option is checked.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c3/Add-a-Drop-Down-Box-in-Excel-2007-Step-9-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-9-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ c/c3/Add-a-Drop-Down-Box-in-Excel-2007-Step-9-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-9-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

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

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/5/56/Add-a-Drop-Down-Box-in-Excel-2007-Step-10-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-10-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 5/56/Add-a-Drop-Down-Box-in-Excel-2007-Step-10-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-10-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click the “Error Alert” tab to display an error message if the data entered in the box containing the drop-down box is invalid. You need to make sure that the “Show error alert after invalid data is entered” box is checked. To display a warning or information but not prevent invalid data entry, select “Warning” or “Information” from the “Style” drop-down list pane. To display a message and prevent invalid data entry, select “Stop” from the “Style” drop-down list pane. Enter a title in the “Title:” field and the text of the error message you want to display in the “Error message:” field.

{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/94/Add-a-Drop-Down-Box-in-Excel-2007-Step-11-Version-3.jpg/ v4-728px-Add-a-Drop-Down-Box-in-Excel-2007-Step-11-Version-3.jpg”,”bigUrl”:”https://www.wikihow.com/images/thumb/ 9/94/Add-a-Drop-Down-Box-in-Excel-2007-Step-11-Version-3.jpg/v4-728px-Add-a-Drop-Down-Box-in-Excel-2007- Step-11-Version-3.jpg”,”smallWidth”:460,”smallHeight”:345,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output “></div>”}

Click “OK” to save the validation criteria and create a drop-down box.

Advice

If the drop-down list item is longer than the cell it contains, you can change the cell width to display the full text.
To delete a frame or drop-down list, click on the cell containing the frame/drop-down list. Click the “Data” tab in the Microsoft Excel 2007 ribbon. Click the “Data Validation” button in the “Data Tops” tool group. Click the “Settings” tab, click the “Clear All” button, and then click “OK.”

Steps

Advice

Related Posts