How to Create Data Validation in Microsoft Excel?

Microsoft Excel is one of the most extensively used tools in organizations. No matter you are an engineer or data scientist, at the end you need to have numbers on Excel sheet to present your work. Among many other features in Excel, data validation is one of the common features to use in a team or group environment. For example, you can send an Excel to all your colleagues to fill from the restricted values. This will help you to avoid entering free text and make the data within a specified scope. In this article, let us explore how to create data validation in Microsoft Excel with various options.

1. Defining Validation Rules

Excel allows the setting of validation rules for individual cells or cell ranges. You can make use of this to monitor the acceptance of specific values only in particular cells.

Let us take an example for validation of a student mark sheet.

The cells below the “Subject 1” column should be a number.
The allowed value is between 0 to 100.
It cannot be a text, less than zero or greater than 100.
Excel should reject all other entries automatically.

You can define the data validation rules in Excel by following the below steps.

In order to set these validations, first select the range of cells you want to restrict the values. In our example, we select cells D4 to D7.
Go to “Data” tab and select “Data Validation” option under “Data Tools” group.

This will open up the “Data Validation” dialog box. You can setup allowed entries for the selected cells under the “Settings” tab of the dialogue box.
Click on the dropdown under “Allow:” and select your preferred value. In our example, we will select the whole number for marks.

In the “Data” list box you can define validity conditions. This limits entry possibilities and we select “between” option as we need to only allow the values from 0 to 100. Remember, the options will change based on your selection. For example, if you select “greater than” you will only see a text box to enter a “Minimum” value.
For the condition “between” you can determine the possible span in the “Minimum” and “Maximum” boxes. We will enter the minimum as 0 and maximum as 100.

Click on the “OK” button and now that you have setup a data validation for the selected cells successfully.

2. Validation Rule Error Alert

Using a validation rule, you can restrict the allowed values in a cell. When you try to enter a value 110 in the D4 cell, Excel will show you an error message and restrict you.

However, the error message does not show you the allowed range is 0 to 100. In order to make the error message more meaningful, you can create custom error alert message that gives you clear instruction when the error occurs.

Select the cell or range of cells and go to “Data > Data Validation” as explained above. We recommend you to do this step together so that you can setup validation and error message in single step.
When you are on the “Data Validation” dialog, go to “Error Alert” tab.
In the “Title” box, enter the heading for your error.
Enter the details of the rule under the “Error message” pane.

The important part is to set the error message style. Click on the “Style” dropdown and you can see three options – Stop, Warning and Information. Depending on your need you can completely restrict the cell value or allow the users with warning or information message. For example, when the user enter 110 in our case you will get the following errors for different alert styles.

2.1. Stop Error Alert:

This is also the default alert that appears when you do not setup any custom error alert messages. Excel will not allow you to enter the invalid entries and you should type the allowed values.

2.2. Information Error Alert:

This is a simple information alert and Excel will allow you to continue with the invalid value when you click on “OK” button.

2.3. Warning Error Alert:

This is a warning and Excel will offer you options to continue or cancel.

3. Validation Rule Input Message

Just like an error alert, you can also create a display message when clicking on the cell. This will help to type the correct data one-step before getting the error.

Select the cell or range of cells and go to “Data > Data Validation” as explained above.
When you are on the “Data Validation” dialog, go to “Input Message” tab.
In the “Title” entry box, enter the message heading for your error.
Enter the details of the rule under the “Input message” pane.

Click on the validated cell to see the input message appears like below as an instruction.

This will be useful when you have a large Excel with different kinds of data validation across different cells.

4. Validation Rules of Existing data

When you create a validation rule, it is unlikely that your Excel table is still empty. Therefore, you have to take care for a few settings when using validation rules on existing data.

4.1. Extending Data Validation

When you select cells outside validation rules and click on the “Data Validation” option, Excel will prompt you with a message.

You can click on “Yes” to extend the validation to the selected cells. However, the problem is that Excel will NOT validate any of the existing data with your validation rules. You can only use a validation rule for entries that follow a previously set rule. Excel will ignore the existing data without any validation. The only option available in Excel is to label the invalid data so that you can manually correct them.

4.2. Labeling Existing Invalid Values

In order to find the invalid existing data cells, go to “Data” menu and click on the “Data Validation” dropdown button under the “Data Tools” group. Now, select the entry “Circle Invalid Data” to highlight the invalid cells with red circles like below:

4.3. Removing Labeling on Invalid Data

Using validation circles, you can create a clear overview of invalid data in your table. If you have corrected the values or you do not need the circles anymore, you can easily remove them.

You can again use the entry “Clear Validation Circles” under “Data Validation” dropdown to remove the red circle labeling.

5. Validation Rule List

As an alternative to validation rules, you can use a list containing all valid values. Therefore, every user can simply choose between the available values and insert the correct data. In other words, you can restrict the user to select within the specified values. In our example case, you can only allow the marks like 50, 60, 70, 80 and 90 and restrict any other user input.

When you are in the “Data Validation” pop-up, go to “Settings” tab:

Select “List” entry from the “Allow” dropdown box.
Select the “In-cell dropdown” option to show the values in the dropdown.
Check or uncheck “Ignore blank” option based on your need.
Enter the allowed values in the “Source” text box with comma separated. If the required values already exist in a table or in other sheets, after clicking in the “Source” box, select the corresponding area to adopt the values. Ensure the source for the values are available within the same Excel book so that it will work when you share the Excel with others.
Click “OK” button to save your changes.

The cell formatted in this way receives a list arrow upon activation. You can now either enter one of the possible values manually or open the list via the arrow button and then select the desired entry.

You can also setup input message and error alert as explained above.

Use the “Clear All” button to remove all data validation, input messages and error alerts with one click.

How to Create Data Validation in Microsoft Excel?

1. Defining Validation Rules