What is Data Validation in Excel:

Data validation is the function in Excel that is used to control what data can be input in a particular cell or range of cells.

When inputting the data, data validation can display a message for the user regarding what type/kind of data can be input.

It also stops the user from inputting the data other than that allowed by the data validation function and can give an error message when invalid data is entered.

Where to find Data Validation in Excel:

Go to:

Data > Data Tools > Data Validatoin

1. Data

2. Data Tools

3. Data Validation

Data Validation Criteria:

Data validation function in excel can be used to limit data input to following criteria:

1. Any value: This is the default value set by Excel for Data Validation, if there are no Data Validation restrictions, we can enter any value in any cell of excel spreadsheet.
2. Whole number: Allowing only to input a whole number in the cell
3. Decimal: Allowing only decimals in the cell
4. List: Allowing the input of data from a pre-defined list.
5. Date: Allowing input of data in date format only.
6. Time: Allowing input of data in time format only.
7. Text length: restricting the number of text characters, that can be input in the cell.
8. Custom: Specifying a custom formula to restrict the input of data according to that formula only.

How to use Data Validation function in Excel:

Let’s take an example here, where we are inputting expenses relating to a project in a table in Excel, and we want that our cells in the table only allow a particular type of data to be input in the cells to avoid any mistakes.

We have following fields in the table to populate.

We can use different data criteria in these columns to allow particular set of data to be input in these, by using the data validation function.

How to use restrict data from dropdown list only using data validation:

Continuing with the above example, we want our “Expense Type” column to be populated using a dropdown list of following categories:

1. Marketing
2. Entertainment
3. Vehicle running
4. Printing & Stationery

How to add a dropdown list using data validation function in Excel:?

Step 1. Select the cell or range of cells where we want the data validation function to allow only drop down list.

Step 2. Go to data validation function.

Step 3. In validation criteria, select “List”.

Step 4. In source field, either write down the list by separating each item in list using coma.

Or, you can put your list somewhere in excel, and put the range of cells containing list in the source field.

Step 5. Click OK to complete the data validation function.

How to use data validation function to customize date entry in excel?

Continuing with our sample table from the table above, in the column “expense date” we want only dates relating to the month of March, 2020 to be entered. We can use data validation function in excel to restrict that only the dates between March 01, to March 31 can be entered in this column.

Step 1. Select the cell or range of cells where we want data validation function.

Step 2. Go to data validation function.

Step 3. In validation criteria, in “Allow field” select “Date”.

Step 4. In the “Data” field, select “between”.

Step 5: Give the desired start and end dates, which in our case are March 1, 2020 & March 31, 2020 respectively.

Step 6: Click OK to complete the data validation function for date restriction.

How to add an input message using data validation:?

Data validation function allows to add an input message on the cell or range of cells where data validation function has been applied, to give the user a guidance on what type/kind of data can be entered.

Step 1. Go to data validation.

Step 2. Go to tab “input message”.

Step 3. Add Title and the message you want to display when the cell or range of cells is selected.

How to add an error message in data validation function:

Data validation function in excel allows to add an error alert to be displayed, if invalid data is entered in the cell or range of cells.

Step 1. Go to data validation function in excel.

Step 2. Go to the tab “Error Alert”.

Step 3. Select style, title and the error you want to be displayed once the invalid data is entered into.