Use Lists to Limit Data Entered Into a Cell

Data validation rules can help limit the data entered into a cell. However, data entry is quicker and more accurate when you restrict values in a cell to choices from a drop-down list. In other words, you create a data validation list to prevent spelling errors and limit data entries to a list of approved values

1.   Create a list of valid entries from the drop-down list

TIP  Type the list on a sheet in a single column or row without blank cells

2.   Select the cells where you want to prompt users to enter valid data entries

3.   From the Ribbon, click Data tab, go to Tools group, click Validate list button and choose Data Validation

NOTE     If the validation command is unavailable, the sheet might be protected or the workbook may be shared. You cannot change data validation settings if your workbook is shared or your sheet is protected

4.   Use tools in the Data Validation dialog box to create a data validation rule

  • Select Settings tab
  • Click Allow list arrow button and choose List
  • In the Source field, click the Collapse dialog (Selection button) and the dialog box minimizes to make the sheet easier to see

 

  • Select the list of valid entries on the worksheet
  • Press return or click Expand dialog button to restore the Data Validation dialog box
  • Click OK

 

TIP

  • You can also type values directly into the Source box, separated by a comma.
  • To modify the list of valid entries, simply change the values in the source list or edit the range in the Source box.
  • You can specify your own error message to respond to invalid data inputs. On the Data tab, click Validate, and then click the Error Alert tab.

5.   The cell has with a list validation rule applied to it will be indicated with a list arrow when the cells is selected

6.   Click the list arrow to get a list of values that you can enter into the cell

NOTE     If users try to type in a value that is not on the list, the error message dialog box will appear to indicate that the value entered is not from the required list. Click Cancel to select a valid entry from the approved list