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:
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:
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.
2) Select the range of cells or column where drop down option is required:
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”
4) In Data Validation drop down select: “Data Validation”
5) Then select “List” from settings tab within “Data Validation” as shown below:
6) Hit the colourful square below titled “Source” as shown below:
7) After clicking the source option, choose list of values from H column:
8) Click OK.
9) There we go because we have finally implemented the Data validation on our desired column: