Home » Apply data validation in Excel

Apply data validation in Excel

by Online Tutorials Library

Apply data validation in Excel

Apply data validation on your Excel worksheet to stop the user from entering invalid data into Excel cells. In the previous chapter, we briefed about the data validation, its advantages, types, and all the desired information about the data validation. Now, we will show you how actually the data validations are implemented on an Excel worksheet.

In this chapter, we will discuss each validation step-by-step with an example. Thus, till the end of the chapter, you will have no doubt. Topics that we are going to be covered in this chapter are –

Data validation types

Following are the three categories of validation that every Excel user uses in their Excel sheet. For the detailed information, go through with our previous chapter (Data Validation in Excel).

  • Predefined data validation (Number, Decimal, Text length, date, and time validation)
  • Dropdown list data validation (List validation)
  • Custom data validation (User-defined validation)

Although there are various other validations that a user needs, it comes under predefined data validation, such as – Number, Text, length, date, and time validations. We will describe one validation of each type.

Apply number Validation on a column

We will take an Excel worksheet and put the Number validation in one of its columns to restrict the input. Here, we have the following steps to apply number validation on a column in an Excel worksheet.

Step 1: Open an Excel sheet and define the names of columns to indicate the users to enter the data in them.

Apply data validation in Excel

Step 2: Now, select one column on which you would like to apply number validation. E.g., Age column.

Apply data validation in Excel

Step 3: Navigate to the Data tab in the Excel menu bar and click on the Data Validation under the Data tools.

Apply data validation in Excel

Step 4: A default data validation panel will open, where you will see three tabs, i.e., Settings, Input Message, and Error Alert.

Apply data validation in Excel

Step 5: In the Setting tab, select a validation from the Allows dropdown list. We will choose the Whole number that will enable few more settings on the panel.

Apply data validation in Excel

Step 6: Now, choose one of the conditions from the Data dropdown list. For example, if you select greater than or equal to condition, you have to enter one more information with it.

Apply data validation in Excel

Step 7: Define the minimum value in the respective field for the greater than condition.

Apply data validation in Excel

According to this condition, the value entered by the user in the validated column must be greater than or equal to 20. It means the minimum age of an employee must be 20.

Note: Don’t press the OK button yet.

Set an Input Message

Step 8: Navigate to the Input Message tab just next to the Setting tab.

Apply data validation in Excel

Step 9: Provide a title and a custom message here that will display while selected validated cell.

Apply data validation in Excel

Set an Error Alert

Step 10: The last step is – navigate to the Error Alert tab. Choose one of the Style from the list, either Stop, Warning, or Information.

Apply data validation in Excel

Step 11: Now, enter the title and alert message in the respective fields to show an alert when a user enters the wrong value.

Everything is set up successfully; click the OK button to save all the changes.

Apply data validation in Excel

Number validation is applied to the age column successfully. Enter some data to the validated column and check the validation.

Output 1: Input Messages

When the user selects the cell with validation, Excel displays a message that is set while putting validation on the cell. This message contains what type of value you can enter in the validation column.

See the following output:

Apply data validation in Excel

Output 2: Correct Input

When a user provides correct input in the validated column and presses the Enter key, no error occurs and data is accepted successfully. You will get the output as showing below:

Apply data validation in Excel

Output 3: Alert Message

The last output is an alert message. It shows when the user enters an incorrect value in the validated cell and presses the Enter key. See the output on entering a wrong value.

Apply data validation in Excel

Here, either Retry or enter the value again in the same cell or Cancel the current input.

Output on entering wrong data type

Despite the Input message, if you also try to enter text input in number validated field, value is not acceptable by the cell and it will show the alert message to you.

Apply data validation in Excel

Hence, all the validations work in the same ways. But the way and place of use are different.

This method of putting validation on a column is totally different than all the other validation. You will see how it is different when we will show you.

It is an input validation that presents predefined choices to the users to provide the input to an Excel worksheet. Hence, you have the only option to provide input to an Excel cell is to choose input value from the dropdown list.

Here, we have the following steps to apply list validation on a column in an Excel worksheet.

Steps to create list validation

We have added one more column (Gender) to the previously used Excel sheet to define this validation.

Apply data validation in Excel

Step 1: Select this Gender column and navigate to the Data tab in the menu bar.

Apply data validation in Excel

Step 2: Here, click on the Data Validation under the Data tools section.

Apply data validation in Excel

Step 3: In the setting tab, click on the Allow dropdown button and choose List from it to create a dropdown list for the gender column.

Apply data validation in Excel

Step 4: In the same setting tab, provide the values separated by commas in the Source field. These values will display next to each cell of the validated column.

Apply data validation in Excel

Step 5: Now, if you want to provide any input message, move to the respective tab and enter a custom message here.

Apply data validation in Excel

Step 6: Similarly for the Stop, Warning, or Information alert on wrong input, navigate to the Alert tab in the data validation panel.

Apply data validation in Excel

Step 7: Make all the steps done and click the OK button to complete the list creation/validation process.

Apply data validation in Excel

A dropdown list is created for each cell input of the gender column. Hence, the column is validated.

Step 8: See the below output of how a dropdown button is added next to the validated cell, which enables when the cell is selected.

Apply data validation in Excel

Step 9: Click on the dropdown list and choose the correct gender from the list to provide the input in the gender field.

Apply data validation in Excel

Step 10: See that value is entered without any interruption.

Apply data validation in Excel

Create and apply custom Validation

Besides all these built-in validation rules, you can also set your custom validations using your own formulas. These custom validations allow the users to define their own validation, which is not available in the predefined validation list.

Custom validation enables the users to write their own formulas to validate the data. Thus, it requires a formula to be entered by the creator. For example, custom validation to validate the email address.

Steps to create custom Validation

Here, we have the following steps to create custom validation and apply them on a column of Excel worksheet. Before creating email custom validation, we will add an email column to the previously used Excel worksheet.

Apply data validation in Excel

Step 1: You open your Excel sheet in which you want to define custom validation. Select a column for applying the custom validation and navigate to the Data tab.

Apply data validation in Excel

Step 2: Here, click on the Data Validation under the Data tools section.

Apply data validation in Excel

Step 3: In the setting tab, click on the Allow dropdown button and choose Custom from the list to define custom validation.

Apply data validation in Excel

When you select Custom from the list, it will enable one more field (Formula) to define your own formula.

Step 4: Here, Enter the formula in the Formula field to create a custom condition. As we are creating email validation, write the following formula for it:

ISUMBER(FIND(“@”, F)

Apply data validation in Excel

Step 5: Now, similar to the other predefined validations, navigate to the Input message tab to give a pre-input instruction to the users before entering the data into the cell.

Apply data validation in Excel

We have only provided input message here, not the title.

Step 6: Go to the last Error Alert tab and same define the alert message here.

Apply data validation in Excel

Custom email validation has been created successfully and applied on column F (Email) in the Excel worksheet.

Output 1: Invalid Email

When the user enters an invalid (without @ sign) email address and presses the Enter key. Email is not accepted by validated Excel cell and an error alert displays.

Apply data validation in Excel

Click on the Retry button to re-enter the email address to the F2 cell.

Output 2: Valid Email

Now, enter a valid email address along with @ symbol, and see the outcome. Email is now accepted by validated Excel cell and no error alert popup displays.

Apply data validation in Excel

See that the email has been accepted by the validated Excel cell.

Apply data validation in Excel

Find cells with validation

Often, when we do not pass the Input message and alert message to the validated cells, they do not interpret whether the cell is validated or not. In that case, a problem arises to find the cells with validation.

Excel enables a feature for the users to find all the cells having validations with the help of GO TO. It will highlight all the validated cells and columns.

Below are some simple steps to get know all the validated cells in the Excel worksheet:

Step 1: Open your Excel worksheet for which you want to find validated cells.

We have this worksheet with some validations.

Apply data validation in Excel

Step 2: Press the Ctrl+G (shortcut key for Go To) to open the Go To panel, where click on the Special button present at the bottom left corner.

Apply data validation in Excel

Step 3: In Go To Special, mark the Data Validation radio button in the list.

Apply data validation in Excel

Leave All under the Data Validation radio button as marked to see all validations and click the OK button now.

Apply data validation in Excel

Step 4: Look in the below Excel screenshot that all the cells/columns highlighted with grey color are validated cells.

Apply data validation in Excel

See that the column B (EMP_NAME), C (AGE), and E (GENDER) has been highlighted. It means these are the column on which certain validations are applied.

Hence, this is the way to find the cells with validation in an Excel worksheet.

Copy validation from one column to another column

Several times, we need the same validation to be put on more than one column. In that case, it is good to copy the validation from one column to another rather than putting it separately on each column.

Copying the validation saves the time of users to put it on each column individually. It is not so complex to copy the validation. It just two steps simple process of copy and special paste.

Now, we will show you with the help of an example how you can do it with your Excel sheet.

Step 1: We have the following excel datasheet. In this example, we will copy age column validation.

Apply data validation in Excel

Step 2: Select the column/cell whose validation you want to copy and press the Ctrl+C shortcut key of copy.

Apply data validation in Excel

Note: “Do not simply paste using Ctrl+P that only paste the data, not validation. This time you have to use Paste Special option of Excel to copy-paste the validation.”

Step 3: Now, go to the column where you want to paste the validation. Here, if you select a single cell, validation will apply only to that cell. If you select the whole column, validation will paste on all cells of the selected column.

Apply data validation in Excel

Step 4: Right-click on the selected column and click on the Paste Special option here.

Apply data validation in Excel

Step 5: A paste special dialogue box will open, where mark the Validation radio button and click the OK button to complete the action.

Apply data validation in Excel

Validation is now copied from one column to another column. You can see by yourself that validation has been copied.

Apply data validation in Excel

Now, this time to learn how one can remove all these validations applied on an Excel sheet.

Clear validation from Excel worksheet

Often, we apply wrong validation to any column. In that scenario, we need to remove that validation from that column. Excel allows the users to clear the validation from the validated column or cells.

You can remove all validations from the entire worksheet or from one column individually. It is totally up to you. Steps are completely same for both. The only difference is – select the entire worksheet for clearing all validations from the entire worksheet or select only a particular column to remove validation only from one column.

Steps to clear validations

We have a few simple steps to clear the validations from the Excel worksheet.

Step 1: Select the range of cells or all cells from which you want to remove validation.

We have selected all cells (entire worksheet).

Apply data validation in Excel

Step 2: Go to Data tab > Data Tools > Data Validation.

Apply data validation in Excel

Step 3: It may ask for the following message, ignore the message and just click on the OK.

Apply data validation in Excel

Step 4: A data validation tab will open, where click on the Clear All button and then the OK button.

Apply data validation in Excel

Step 5: All validations applied on the selected cells have been removed successfully and your validated become normal Excel sheet with no validation.


You may also like