EDUCBA

Home Excel Excel Resources Excel Tools Excel Data Validation

Excel Data Validation

By Arun GuptaArun Gupta

Data Validation in Excel

Excel Data Validation (Table of Contents)

Data Validation in Excel

Data validation is a feature in MS Excel used to control what a user can enter in a cell of an excel sheet. Like, restrict entries in a sheet, such as a date range or whole numbers only. We can even create dropdowns as well, which saves un-necessary space and shows the values in a single cell. Also, we can create a customized message which will appear user insert any incorrect value or an incorrect format.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

As an example, A user can specify a meeting scheduled between 9:00 AM and 6:00 PM.

As we can use data validation in excel to make sure a value is a positive number, a date between 15 and 30, make sure a date occurs in the next 30 days, or make sure a text entry is less than 25 characters etc.

Locate in MS Excel

  • Click on Data Tab in the Menu bar.

Data Validation Step 1

  • Select on Data Validation from the toolbar under the Data Tab:

Data Validation Step 2

How to Create Data Validation Rule in Excel?

Let us understand the working of Data validation in excel by some examples.

You can download this Data Validation Excel Template here – Data Validation Excel Template

Example #1

We can restrict a user to enter a whole number between 0 and 10.

Execute the Below mention steps for the creation of the data validation rule in excel:

Step 1: Select B2 Cell.

Data Validation Example 1-1

Step 2: Go to the Data tab, click on Data Validation from Data in the toolbar.

Data Validation Example 1-2

Step 3: A data validation Pop-Up will open:

Data Validation Example 1-3

Step 3.1: On the Settings tab, Click on Allow drop-down under validation Criteria.

Data Validation Example 1-3.1

Step 3.2: Select the Whole number, then Some more required options will be enabled.

Data Validation Example 1-3.2

Step 3.3: Select between from the drop-down of the Data list, Enter the Minimum and Maximum number for restriction. And click ok.

Data Validation Example 1-3.3

Step 3.4: All settings will apply to the selected cell.

Now Enter 1 in cell B2- It will allow a user to enter any whole number from 0 to 10.

Data Validation Example 1-3.4

Now Enter 11 in cell B2- It will throw by default error.

Data Validation Example 1-3.5

Example #2 – Set Input Message and Error Alert

We can restrict a user to enter a limited text.

Step 1: Select B5 Cell from Example 2 Sheet.

Data Validation Example 2-1

Step 2: Click on the data validation tool from the Data Menu Bar and select Text Length in Allow drop-down.

Data Validation Example 2-2

Step 3: Select equal to from the data list drop-down.

Data Validation Example 2-3

Step 4: Enter your desired length of the password (as an example 11).

Data Validation Example 2-4

Step 5: Click on the Input Message tab and provide a message displayed on the selection of cell.

Data Validation Example 2-5

Step 6: Click on the Error Alert tab, provide a Title and the Error message, which will be displayed if a user entered an invalid length password.

Data Validation Example 2-6

Step 7: After that, click the Ok button, the setting will be applied to the selected cell.

On the Selection of Cell, an Input message will be displayed.

Data Validation Example 2-7

Enter a text which has a text length of 11. It will be selected successfully.

Data Validation Example 2-8

Now enter an invalid password. It will throw the described error message.

Data Validation Example 2-9

Example #3 – Custom option for e-mail address validation

Step 1: Select the C2 cell, Go to Data Tab and click on Validation data in the toolbar.

Example 3-1

Step 2: Select custom in Allow drop-down.

Example 3-2

Step 3: Write a formula for selecting only value if it is having ‘@’.

Example 3-3

Step 4: Click on the Input Message tab, provide the message, which will be displayed on the selection of a cell.

Example 3-4

Step 5: Click on the Error Alert tab, provide the Title and an Error message, which will be displayed if a user entered an invalid length E-mail Id.

Example 3-5

Step 6: After that, click the Ok button, the setting will be applied to the selected cell.

On the Selection of Cell, the Input message will display.

Example 3-6

Enter a valid e-mail id – It will be selected successfully.

Example 3-7

Now enter any invalid e-mail Id- It will throw a described error Message.

Example 3-8

Valuation criteria of excel data validation Settings

  • Any Value – To provide any type of data.
  • Whole Number – Enter only whole numbers.
  • Decimal – Enter only decimal numbers.
  • List – Pick data from the drop-down list.
  • Date – Accept the only date.
  • Time – Enter only time.
  • Text Length – Fixed the length of the text.
  • Custom – Enter a custom formula.

Conditions in data list in Valuation criteria settings

Between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to.

Some more condition of excel data validation Settings

  • If a user wants to ignore the blank, then there is a checkbox called Ignore.
  • If a user selects between, then a user needs to select the Minimum and Maximum values for the cell(s).
  • If a user wants to apply these changes to all other cells with the same setting, then there is a checkbox that needs to mark, and it will apply to the remaining cells in the sheet.

When to Use Data Validation in Excel?

  • Best used when a user wants to share a sheet with another user, and he wants the data entered to be accurate and consistent.
  • Restrict entries to predefined items in a list.
  • Restrict numbers outside a specified range.
  • Restrict dates outside a certain time frame.
  • Restrict times outside a certain time frame.
  • Limit the number of text characters.
  • Validation of the data, which is available on other sheets or workbook.
  • Choose to be displayed an Input Message when a user clicks on a cell as a user’s guide.
  • A user can customize the error alert; it can be anything as per a user-defined.

Things to Remember About Data Validation in Excel

  • If a user wants to create a data validation rule in the workbook for excel web apps or Excel services, then he needs to create an excel data validation rule on the desktop first.
  • If one user sender a workbook to another, then first, a user needs to make sure that the workbook is unlocking; otherwise second, a user is never able to access the workbook cells to fill value.
  • Always make in mind that there is no error in a formula like #REF! or #DIV/0!
  • Anyone who can create a data validation rule in the filled cell MS excel will not detect it.

How to Remove the Excel Data Validation rule?

A user can remove excel data validation from a cell, select the cell, click Data Validation, and then click Clear All.

A user can modify or delete the excel data validation rule only if the inherited sheet is unprotected. If it is password protected, contact the owner of the workbook. Only he can help to unprotect the workbook. As there is no way to recover or lost the password option in MS Excel.

Recommended Articles

This has been a guide to Data Validation in Excel. Here we discuss how to create Data Validation in excel along with excel examples and a downloadable excel template. You may also look at these useful functions in excel –

  1. Excel Data Visualization
  2. Excel Database Template
  3. Excel Data Filter
  4. Excel Data Bars
0 Shares
Share
Tweet
Share