Create a Drop Down List in Excel

If you add drop down lists in your Excel worksheets, it will be easier to enter data.

For example, create an order form with a drop down list of products, so you can click on the product that you want, instead of typing. This helps prevent data entry errors, such as spelling mistakes.

Note: Data validation is not foolproof. Users can get around your data validation rules by pasting data into the cell, or by choosing Edit|Clear|ClearAll to clear the settings.

This short video show you how to use Excel's Data Validation feature to create a drop down list.

The key steps in setting up a data validation drop down are:

A) Set up the list of items:

-On the worksheet, type the list of items that you want in the drop down list.

-If the data validation drop down lists will be on a different worksheet, you must name the list of items.

B) Create the drop down lists:

-Select the cell(s) where you want the drop down list.

-On the Ribbon, click the Data tab, then click Data Validation.

-For Allow, select List

-In the Source box:

-If the list of items is on the same worksheet as the drop down list, select the worksheet cells where you typed your list

-If the list of items is a named range, type an equal sign, and then type the list name. For example: =ProductList

-Click OK.

C) Test the data validation drop down lists:

1.To use the drop down list, click in a cell where you added the data validation.

2.Click the drop down arrow, and then click on an item in the list.

Note: Only the active cell will show a drop down arrow, if it has data validation. The arrow will not be visible in other cells, until you click on them. To help people identify the cells that have a drop down list, you can use a colour code. For example, add light green fill colour to the data validation cells.

Data validation is a very powerful feature in Excel, and will make life easier for you and anyone who uses your Excel files. You can even make the items in a data validation list change, depending on what was selected in a related drop down list, such as State and City.


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: