If you want to validate a cell to restrict data entry to values in a drop-down list follow these steps:
- Select the cell you want to validate.
- On the Data tab, in the Data Tools group, click Data Validation.
- The Data Validation dialog box will open.
- In the Data Validation dialog box, click the Settings tab.
- Click on the Allow box then select List from the drop-down list.
- Click the Source box and then type the valid values separated by the appropriate list separator character depending on your installation( usually a comma “,” or semicolon “;”). For example if the cell is for a color of a car then you can limit the values by entering : Silver, Green, Blue.
- Instead of typing your list manually, you can also create the list entries by referring to a range of cells in the same worksheet or another worksheet in the workbook.
- To specify the location of the list of valid entries, do one of the following:
- If the list is in the current worksheet, enter a reference to your list in the Source box, for example enter: =$A$1:$A$6.
- If the list is on a different worksheet, define a name for your list then enter the name that you defined for your list in the Source box, for example, enter: =ValidProjects.
- Make sure that the In-cell dropdown check box is selected. Otherwise, you won’t be able to see the drop-down arrow next to the cell.
- To specify how you want to handle blank (null) values, select or clear the Ignore blank check box.
- If your allowed values are based on a cell range that has a defined name and there is a blank cell anywhere in that range, selecting the Ignore blank check box allows any value to be entered in the validated cell. This is also true for any cells that are referenced by validation formulas: if any referenced cell is blank, selecting the Ignore blank check box allows any value to be entered in the validated cell.
More Validation options:
- Display an input message when the cell is clicked.
- Click the Input Message tab.
- Select Show input message when cell is selected check box.
- Fill in the Title and text for the Input message.
- Display an error message when wrong data is entered.
- Click the Error Alert tab.
- Select Show error alert after invalid data is entered check box.
- Fill in the Title and text for the Error message.
- Select one of the following options for the Style box:
- Information: Display an information message. Does not prevent entry of invalid data.
- Warning: Display a warning message. Does not prevent entry of invalid data.
- Stop : Prevent entry of invalid data.
If you change the validation settings for a cell, you can automatically apply your changes to all other cells that have the same settings. To do so
- Open the Data Validation dialog box.
- Click the Settings tab.
- Select the Apply these changes to all other cells with the same settings check box.