Microsoft Office Tutorials and References
In Depth Information
To create a drop-down list in a cell, follow these steps:
1. Type the list items into a single-row or single-column range. These items are the ones
that appear in the drop-down list.
2. Select the cell that will contain the drop-down list and access the Data Validation
3. In the Settings tab, select the List option and specify the range that contains the list using
the Source control.
4. Make sure that the In-Cell drop-down check box is checked.
5. Set any other Data Validation options as desired.
After performing these steps, the cell displays a drop-down arrow when it’s activated. Click the
arrow and choose an item from the list that appears.
If you have a short list, you can enter the items directly into the Source control in the
Settings tab of the Data Validation dialog box. (This control appears when you choose
the List option in the Allow drop-down list.) Just separate each item with the list
separator specified in your regional settings; use a comma if you use the U.S. regional settings.
If you specify a range for a list, the range must be on the same sheet. If your list is in a
range on a different worksheet, you can provide a name for the range and then use the
name as your list source (preceded by an equal sign). For example, if the list is on a
different sheet in a range named MyList , enter the following:
Using formulas for data validation rules
For simple data validation, the data validation feature is quite straightforward and easy to use,
but the real power of this feature becomes apparent when you use data validation formulas.
The formula that you specify must be a logical formula that returns either TRUE or
FALSE. If the formula evaluates to TRUE, the data is considered valid and remains in the
cell. If the formula evaluates to FALSE, a message box appears that displays the
message specified in the Error Alert tab of the Data Validation dialog box.
You specify a formula in the Data Validation dialog box by selecting the Custom option in the
Allow drop-down list on the Settings tab. You can enter the formula directly into the Formula
control, or you can enter a reference to a cell that contains a formula. Note that the Formula
control appears in the Settings tab of the Data Validation dialog box only when the Custom option is