By Colin Wilcox

Have you ever needed to create drop-down lists on a worksheet? This column explains how, and it introduces a larger feature called Data Validation. This is part one of two articles. The next column explains other Excel data validation tools.

Have you ever needed to create drop-down lists or otherwise control the type of data that users enter on a worksheet?

(Hint: I know you have. I read your letters. You want to limit the choices users make, and you want to limit the types of data that users can enter.)

Drop-down lists provide one form of data control by forcing users to select only the items that you provide. But drop-down lists are only part of a larger Excel feature called data validation. Data validation is the process of applying rules that restrict what users can enter on a worksheet. For example, you can also specify that a cell or range of cells contains whole numbers between 1 and 20 or text strings at least five characters long. In addition, you can write your own input and error messages to help ensure that users enter the correct types of data.

This column explains how to create drop-down lists. The second column in this series explains how to create validation rules for whole numbers, dates, and other types of data, how to use validation formulas, and how to create custom input and error messages. The information in both columns applies to Excel 97 and later.

Try it!

Let's start by manually creating a drop-down list.

To create a drop-down list

1. Start Excel and open a new, blank worksheet.
2. In cell A1, type Parts, and then select cell B1.
3. On the Data menu, click Validation, and then click the Settings tab.
4. From the Allow list, click List.
5. In the Source box, type the following values:

   pistons, valves, sunroofs

    Important   Use the list separator specified in your Microsoft Windows® system settings to separate the values in this list. For information about setting or changing your list separator, see the Power User column titled Putting regular expressions to work in Word.

6. Click OK.

Notice that when you select cell B1, Excel places an arrow next to it to indicate that it contains a list of values. However, when you select another cell, the arrow disappears. That, of course, leads to a question: How do you find cells that contain data-validation rules? Follow these steps:

To find cells containing validation rules

1. Click anywhere on the sample worksheet that contains the list you created in the previous section.
2. Press F5. The Go To dialog box appears.
3. Click Special. The Go To Special dialog box appears.
4. Click Data Validation, and then click OK.

Excel highlights all the cells on the open worksheet that contain data-validation settings.

Creating lists from existing values

Creating drop-down lists manually works fine up to a point, but what if you need to create several drop-down lists in several worksheets? You can speed the process by using values that reside on another worksheet, another section of the same worksheet, or values from data stored in other workbooks. The steps in this section explain how to create each type.

To create a drop-down list from values on the same worksheet

1. Copy this column of data, go to Sheet1 in your sample workbook, and paste the data starting at cell E1.

   8-tracks

   fake fur

   sub woofers

   hood scoops

   fender flares

   spoilers

   window tint

   fuzzy dice

   bobble heads

2. Select the cell that contains the list you created earlier (cell B1, unless you chose otherwise), and then, on the Data menu, click Validation.
3. Replace the list that you typed manually with the following value: =E1:E9.
4. Click OK, and then select a value from the list.

Notice that if the cell contains a value from the previous list that you typed manually, such as "pistons," that value doesn't change until you replace it with a new one.

To create a drop-down list from data on another worksheet

1. Copy the column of data from the previous set of steps, go to Sheet2 in your sample workbook, and then paste the data, starting at cell A1.
2. Select the cells, and on the Insert menu, point to Name, and then click Define. The Define Name dialog box appears.
3. In the Names in workbook box, type TastefulParts, and then click OK. Excel applies the name TastefulParts to the selected range of cells.
4. Click the Sheet1 tab, select the cell that contains the list you created earlier (B1, unless you chose otherwise), and then, on the Data menu, click Validation.
5. Replace the value that you typed in the previous set of steps with: =TastefulParts.
6. Click OK, and then select a value from the list.

To create a drop-down list from values in other workbooks

1. Save your sample workbook. Give it a name you can remember. For the rest of these steps, I'll refer to that file as Workbook 1.
2. Create a new workbook, save it with another clear name, and enter either a column or a row of values in Sheet1. Any values will do. For the rest of these steps, I'll refer to this file as Workbook 2.
3. Go to Workbook 1 and select the cell in which you want to create your drop-down list.
4. On the Insert menu, point to Name, click Define, and then, in the Names in workbooks box, type a name for the list.
5. Go to the Refers to box, delete any content, and leave the text pointer in the box. If you don't, you can't proceed.
6. On the Window menu, click Workbook 2, and then, in that workbook, select the cells that contain the values you created in step 2.
7. In the Define Name dialog box, click Add, and then click Close.
8. Go to Workbook 1, select the cell in which you want to create your list, and then, on the Data menu, click Validation.
9. From the Allow list, select List, and in the Source box, type an equal sign (=) followed by the name you created in step 3. Do not insert a space or any other character between the equal sign and the name.
10. Click OK.

The list remains blank until you select a value.

 Note    To use this type of validation, the source and destination workbooks must be open at the same time. For more information about creating lists from values in other workbooks, see Using a validation list in another worksheet or workbook.

About the other options on the Settings tab

As you used the Data Validation dialog box, you probably noticed the Ignore blank and Apply these changes to all other cells with the same setting check boxes, located on the Settings tab. The following list explains the implications of using those options:

• Ignore blank Selecting this option prevents Excel from displaying an error message when it encounters blank cells that contain validation rules.
• Apply these changes to all other cells with the same setting If you select this check box, the changes you make to one cell apply to all the other cells that use the same validation rule. Select this option only when you want to make global changes to a set of cells.

What's next

The next part in this series, Data validation 2: Using other validation tools in Excel, explains how to use the other types of validation that Excel provides. It also shows you how to create input and error messages that can help your users avoid data-entry mistakes.

About the author

• Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, he writes articles and tutorials for Microsoft Data Analyzer.