Excel Data Validation


There is a way to restrict user input in excel.  This can be achieved by Data Validation.

excel_data_validationHave you ever got frustrated by users inputting random stuff as variables and mess up your calculations ? Or do you want to give users a more convenient way of entering data, that doesn’t include typing? If so, data validation is the tool for you.

With data validation, you can restrict data input for the cells you choose, you can display custom messages when certain cells are selected or you can display custom error messages in case of wrong data input into certain cells.

Data validation button is located on Data Ribbon.

Restricting Data Input:

excel_data_validation (1)

When you first open data validation, you will see Settings screen. From this tab you can restrict data input for selected cells. From this tab, you can use one of several options  located in Allow list:

Whole Number and Decimal :  When you select either one of these options, you will be presented with a selection of conditions (between, grater than, etc.) for restricting data input. Depending on the condition you will need to input additional data (min/max values, etc.).

For example if you set it as: Allow: Whole number, Data: greater than, Minimum: 50, users can input only whole numbers greater than 50.

List: When you select this option, you are going to be asked to specify a source (range of cells) for your list. When you specify your source for the list, users will only be able to select values from that list as input. This is by far the most used option for data validation.

For example you want to allow users to select fruits only are in your inventory. For that you need to set a data validation for fruit selection input cell like this:

Allow:List, Source: =Inventory!A2:A11, in-cell drop down (checked).

Now, when a user clicks on the fruit input cell, they will see an expansion arrow on the right side of that cell. When clicked, a drop down list that shows fruits located in A2:A11 range of Inventory sheet. They can not type anything else, can only select from list.

Date and Time: Date and time options are very similar to whole number and decimal options. You set date or time instead of numbers. That is all the difference.

Text Length: This option is also same as above. You need to specify numbers for text length condition you choose.

Custom: When you select this option, you will be asked to input a formula as condition. Depending on the formula you entered, user input into selected cell will be restricted.

displaying Input Message:

You can also display messages when user select certain cells. For this, open Data Validation window and navigate to second tab. In this screen you can specify a titile and a message for displaying. When finished, click ok. Now users will be greeted with an input message when  they select that cell. Message will look like a comment.

Displaying Error Message:

You can also display messages when user inputs erroneous data into certain cells. For this, open Data Validation window and navigate to third tab. In this screen you can specify a title and a message for displaying, plus an icon for your error message. When finished, click ok. Now if a user inputs a data that doesn’t match with your specifications, he/she will get your error message.

Circle Invalid Data:

You can also use data validation to point out invalid data in your cells. While you can restrict data entry to your cells, sometimes a macro can input invalid data or a formula in that cell can result an invalid value. To check your worksheet against such issues, you can use Circle Invalid Data tool which is located under Data Validation button (in drop down menu).

When you click this button, excel will check your worksheet for invalid data and put a red circle around them if finds any. Circles will disappear either when you correct that data and make it valid or if you click Clear Validation Circles button just bellow Circle Invalid Data button.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *