Data Validation MS Excel drop down list

MS Excel Data Validation and restricting user to choose from a list of given values; restrict data entry in excel cells:

Data Validation MS Excel drop down list: There are several occasions when we need to stop or restrict the users from writing directly in the excel cells so that there are no typographical errors. We provide users with a drop down list of values to choose from; hence, a user can not writing data into excel cells directly. This helps when we wish to count specific type input provided by the user. For example we have the below table and want user input:

MS Excel Data Validation-1

MS Excel Data Validation-1

Let us say that we want the user to input status from a desired list of following types: Paid, Unpaid, Partial Payment and NA.

If we don’t restrict the user, there may be numerous typographical and situational errors in the received data. Therefore, we must restrict the user to select from the given list only one of the value against each client. A typical format generally is:

How to use data validation MS Excel

How to use data validation MS Excel

In above example the user can select only from the listed values in a drop down. In order to implement the above, we can do the following:

1) Create a list of required values in a column within excel: the below image shows this. We have created the desired list of values in column H.

Excel Data Validation allow entry from list

Excel Data Validation allow entry from list

2) Select the range of cells or column where drop down option is required:

Data Validation choose from list Excel

Data Validation choose from list Excel

In above table we want the drop down list in D column named Status.
3) In Excel top bar Click “Data” and the click “Data Validation”

Ms excel list values for excel user

Ms excel list values for excel user

4) In Data Validation drop down select: “Data Validation”

MS Excel Listed Values dropdown

MS Excel Listed Values dropdown

5) Then select “List” from settings tab within “Data Validation” as shown below:

Excel data validation list

Excel data validation list

6) Hit the colourful square below titled “Source” as shown below:

MS excel restrict user with data validation

MS excel restrict user with data validation

7) After clicking the source option, choose list of values from H column:

Using data validation in excel

Using data validation in excel

8) Click OK.
9) There we go because we have finally implemented the Data validation on our desired column:

How to use data validation MS Excel

How to use data validation MS Excel