How to use the Excel Data Validation Feature

One of the best features that Microsoft Excel offers for people who are really serious about their spreadsheets is the Data Validation option. This function allows users to control the data that is being inputted into their spreadsheets to make sure that no incorrect entries are made. Using the function, you can make it possible for your spreadsheet to allow only entries that meet a certain criteria that you have assigned.

Here’s how it works.

Allowing only certain types of numbers

If you want to only allow certain types of numbers in your spreadsheet (whole numbers, decimals, dates, etc), you can use the Data Validation function to do so.

Select a cell. Go to the “Data” tab and click on the Data Validation icon and then on the Data Validation option.

data validation excel

Now you can select what type of numbers you want to allow from the drop-down list.

data values excel

Creating input error messages

You can use the feature to have Excel warn you with a message when an incorrect entry is made.

Click on the “Input Message” tab and designate a title for the message and then write the message itself.

data error excel

 

Creating a validation list

This option enables you to create a drop-down list that will limit the data that can be entered into a cell to a list of entries that you have previously created.

First create the data that you want as part of your list. Select it and then head over to the Data Validation function again. Click on “Allow” and select the “List” option.

validation list excel sheet

Now specify the source by clicking on the “Source” icon and then reselecting the data that you have entered.

validate entered data formula

The cell will now have an arrow and your personally created drop-down list accompanying it.

Detecting invalid data

If you apply validation to a cell range that was previously created, these entries will not correct themselves manually. Therefore, you are going to have to find them an edit them yourself.

To do this, click on Data Validation and then Circle Invalid Data.

circle invalid data excel

Now you will see which entries are invalid and be able to correct them manually.