Create a Drop Down List in Excel

0 Comments | Rating: 0 | |

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.

Get the Flash Player to see this player.

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.