Where is the drop down box in excel 2007

Here's a picture of your finished spreadsheet:. In the image above, we can simply select a student from the drop down list - no more typing!

We can also do the same for the Subject and Grade. We now need some data to go in our lists. So, type the same data as in the image below. It doesn't need to go in the same columns as ours. But don't type anything in columns A, B, C or E:. From the Data tab, locate the Data Tools panel. On the Data Tools panel, click on the Data Validation item. We put ours on a sheet called Cities, in range A2:A9. Note that we left out the header row, because we don't want that to be a selection option:.

If you want a message to pop up when the cell is clicked, check the Show input message when cell is selected box, and type a title and message in the boxes up to characters.

If you want a message to pop up when someone enters something that's not in your list, check the Show error alert after invalid data is entered box, pick an option from the Style box, and type a title and message.

Information will show a message with this icon and Warning will show a message with this icon. Note: If you don't add a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell. After you create your drop-down list, make sure it works the way you want. For example, you might want to check to see if Change the column width and row height to show all your entries.

If the list of entries for your drop-down list is on another worksheet and you want to prevent users from seeing it or making changes, consider hiding and protecting that worksheet. For more information about how to protect a worksheet, see Lock cells to protect them.

If you decide you want to change the options in your drop-down list, see Add or remove items from a drop-down list. To delete a drop-down list, see Remove a drop-down list. You can download an example workbook with multiple data validation examples like the one in this article. You can follow along, or create your own data validation scenarios. Download Excel data validation examples. Data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list.

Start by making a list of valid entries on a sheet, and sort or rearrange the entries so that they appear in the order you want. Then you can use the entries as the source for your drop-down list of data.

If the list is not large, you can easily refer to it and type the entries directly into the data validation tool. Create a list of valid entries for the drop-down list, typed on a sheet in a single column or row without blank cells. Selecting the entire column will place a formula in the "Data Validation" window that shows the entire column is selected. Open the "Data Validation" window and click OK. Now when you click on the icon next to the cell you will see it contains the values in the column that you set up.

If you add other values into the column they will automatically appear in your cell. Want to master Microsoft Excel and take your work-from-home job prospects to the next level? To remove it, open the worksheet containing the drop-down list's items, and delete them. Now you know the basics of Excel drop-down lists. In the next article, we will explorer this topic further and I will show you how to create dependent drop down lists with conditional Data Validation and how to create a drop-down box from another workbook.

Please stay tuned and thank you for reading! Creating a drop down list in Excel: static, dynamic, from another workbook by Svetlana Cheusheva updated on July 26, How to create a drop-down list in Excel Make a drop-down list from another workbook How to fix Excel data validation not working Additional options for the Excel drop-down box Display a message when a cell with the dropdown is clicked Allow entering other data in a combo box How to edit an Excel drop down list How to delete a drop-down box How to create an Excel drop-down list On the whole, there are 4 ways to make a drop down menu in Excel, and all of them have their own strong and weak points.

Below you will find a quick outline of the main advantages and drawbacks as well as the detailed step-by-step instructions for each method: Drop-down box based on a list of values Drop down box based on a named range Excel data validation list from table Drop down list based on a range of cells Create a dynamic dropdown in Excel Creating drop down lists with comma-separated values This is the fastest 3-step way to create a drop-down box in all versions of Excel , , , and Select a cell or range for your drop-down list.

Use Excel Data Validation to create a drop-down list. Enter the list items and choose the options. In the Source box, type the items you want to appear in your drop-down menu separated by a comma with or without spaces. Make sure the In-cell dropdown box is checked; otherwise the drop-down arrow won't appear next to the cell. Select or clear the Ignore blank depending on how you want to handle empty cells. Click OK and you are done! Type the entries for your drop-down list.

For example, let's create a drop-down list of ingredients for your favorite recipes: Tip. It's a good idea to sort your entries in the order you want them to appear in the drop-down menu. A faster way to create a named range in Excel is to select the cells and type the range name directly in the Name Box.

When finished, click Enter to save the newly created named range. For more information, please see how to define a name in Excel. If you are creating a drop-down based on a named range , and that named range has at least one blank cell, selecting the Ignore blank box allows any value to be entered in the validated cell. For the drop-down list from another workbook to work, the workbook with the source list must be open.

Create a named range for the source list. Create a named reference in the main workbook.