How To Create A Data Validation Rule in Microsoft Excel?

Posted on

Microsoft Excel Blog Banner

Data Validation is a feature in Microsoft Excel that is used to control what a user can or cannot enter in a Cell of the Spreadsheet. It means that we can check the accuracy and quality of the Source Data before using it, importing it or otherwise processing the Data. We can use different types of Validation depending on what we want to accomplish. In this post, I have created something simple enough for everyone to understand what Data Validation is about.

How To Create A Data Validation Rule in Microsoft Excel

In the image below, I have created a Worksheet, that Column A contains the Soccer Clubs, Column B the Country of the Soccer Club. In Column J, I have created an If Function, that if the answer in Column L is correct the Message Correct will be displayed in the Cells J3, J4 and on. If the answer is Wrong, then the Message Wrong will be displayed in the Cell J3, J4 and on. Now in the Column L and specifically the cell L2 I want to enter my Validation Criteria. I want to create a Drop-Down List where someone can select from the List the Number of Total European Trophies, they think that each Club has one. The List with the numbers for my Criteria, I have already created and are in a different Column.

How To Use The Data Validation Feature in Excel

We must select the Data tab and then from the right area of the ribbon we locate the area named Data Tools as shown in the image below.

How To Use The Data Validation Feature in Excel

Once we have located the Data Tools area of the ribbon, we select the drop-down menu of the command Data Validation. Here we can Pick From A List Of Rules To Limit The Type Of Data That Can Be Entered In A Cell. For Example, We Can Provide A List Of Values, Like, 1, 2, And 3, Or Only Allow Numbers Greater Than 1000 As Valid Entries.

How To Use The Data Validation Feature in Excel

Once we select the Data Validation command, the Data Validation dialog box appears as shown below. The Data Validation dialog box, is separated in three tabs and which are the following:

  • Settings Tab: We set the Validation Criteria needed. We select the drop-down menu under the Allow: area, where we can choose from either one of the following: Any Value, Whole Number, Decimal, List, Date, Time, Text Length and Custom.

How To Use The Data Validation Feature in Excel

For my example I have selected from the drop-down menu the Validation Criteria as List. The Data: area is not available, and we also have the choice to activate or deactivate the check boxes next to the commands if we want to Ignore Blank, or to have or not the List to be displayed as In-Cell Drop-Down.

How To Use The Data Validation Feature in Excel

Finally, we must select the Source from where we want the data in our List to be displayed.

In the image above, we notice the area in text pane of the Source Data is =$P$2:$P$80. In order to select the area, we must press the up arrow at the right of the text pane. Once selected, our Spreadsheet will be activated and then we just select with our mouse the area with our Data. As you can see in the image below, Column P contains the Data needed for my Validation Rule.

How To Use The Data Validation Feature in Excel

  • Input Message Tab: Here we have the choice to activate or deactivate the check box at the left of the command Show Input Message When Cell Is Selected. Once activated, When The Cell Is Selected, we can choose to Show The Input Message. We can type in our own Title for the Message to be displayed, and for my example is Correct. We can also type in the text pane the Input Message that we think would be more appropriate.

Keep in mind that if we do not activate the check box at the left of the command Show Input Message When Cell Is Selected, none of the rest of the options will be available.

How To Use The Data Validation Feature in Excel

  • Error Alert Tab: Here we have the choice to activate or deactivate the check box at the left of the command Show Error Alert After Invalid Data Is Entered. Once activated, we can choose When User Enters Invalid Data, to Show the Below Error Alert such as Style. From the drop-down menu, we can select one of the Styles for the Error to displayed as. Then we can type in a Title in the text box for the Error, and in the Error Message text pane to type a Message for the Error such as Try Again in my example.

Keep in mind, that if we don’t activate the check box at the left of the Show Error Alert After Invalid Data is Entered, none of the rest none of the rest of the options will be available.

How To Use The Data Validation Feature in Excel

Whatever adjustments we make, for them to take place we must press the Ok button located at the bottom right corner of the Data Validation dialog box. We can also use the Clear All button, which is located at the bottom left corner of the Data Validation dialog box, to Clear All the changes that we have made no matter at which Tab we are at the moment.

In the next image, the List is displayed, where we must select one the Numbers in the List (which is the Source for our Validation Criteria). In the next image I have selected the Number 22 from the List, which is the correct answer of How Many European Trophies has Real Madrid from Spain won. Once I have selected the correct answer, in the Cell J2 I have created an If Function displaying that I have selected the right answer.

How To Use The Data Validation Feature in Excel

In the next image, I have entered a number that was not in the Source List that I had created of How Many European Trophies Milan from Italy has won. Straight away the Error Alert dialog box appeared with the icon Style that I had chose and the Error Message that I had typed in.

How To Use The Data Validation Feature in Excel

In the image below, we can see the rest of the Teams with the correct or wrong answers on How Many European Trophies they have won. The data in the Column P, is my Source List for my Validation Criteria.

How To Use The Data Validation Feature in Excel

Below you can check out the video describing How To Use The Data Validation Feature in Microsoft Excel.

Don’t Forget To Subscribe To My YouTube Channel.

YouTube Channel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s