Tag Archives: Restrict User Input

Data Validation in MS Excel‒ Creating in-cell drop downs

In this post I’m going to talk about Data Validation in MS Excel. Dashboards require user inputs, based upon which the output is generated. A good dashboard ensures that the users have an easy option to provide their inputs and at the same time have the data entry restricted to a certain set of allowed values that the dashboard recognizes as input. This ensures that no errors occur while data processing due to the wrong input values entered by the user.

We earlier demonstrated the use of combo-boxes in achieving this purpose (read about combo-boxes by clicking here). In this tutorial we will demonstrate how this task can also be achieved by  using the data validation feature of MS Excel.

How does it work?

Compared to combo-boxes which are require insertion on a sheet, data validation can be applied to cells itself . MS Excel allows the following data validation settings to be applied to cells:Restrict data entry to values in a drop-down list

  • Restrict data entry to a whole number within limits
  • Restrict data entry to a decimal number within limits
  • Restrict data entry to a date within a time frame
  • Restrict data entry to a time within a time frame
  • Restrict data entry to text of a specified length
  • Calculate what is allowed based on the content of another cell
  • Use a formula to calculate what is allowed

We’ll discuss only the ‘Restrict data entry to values in a drop-down list’ method here since we’ll be using it the most. Also, once we explain this, it would be pretty simple for you to explore the rest on your own

The following steps will have to be followed to apply data validation and create an in-cell drop-down (cell ‘I1’ in the picture  below)

 

Step 1: Select the cells in which you want to create a drop-down (cell ‘I1’ here; can be a range of cells)

Step 2: On the MS Excel Ribbon, go to Data→ Data Validation → Data Validation…

Step 3: The Data Validation dialog will appear. In the Settings tab, select List under the Allow parameter and select the source data for this list under Source category. The source data here would be $G$2:$G$6 (you can also use a named range)

Crab Tip: While creating Data Validation you can enter the Input Message to display once the cell has been selected by the user and also, you can specify the Error Message that appears when a user enters a wrong value in the cell.

Step 4: Click on ‘Ok’ and you have created an in-cell drop-down with the source data  $G$2:$G$6. Now when you select the cell, you will see a drop-down from which you can select from the range of values that you specified.

Data validation can not only be used to create drop-downs to replace combo-boxes but also for creating forms and data sheets where you would like to restrict user entries to certain specific values— in fact the later is its primary purpose. This would ensure that the user does not input data that the tool cannot recognize and hence, would reduce the chances of errors.

So, the next time you have to take a large number of inputs from the user (like in case of a Market Research based dashboard), don’t forget to limit the options available for entering the data— you don’t want your users to come back to you every time they misspell an entry.