Excel Data Validation (Table of Contents)
- Data Validation in Excel
- How to Create Data Validation Rule in Excel?
- Valuation criteria of data validation Settings
Data Validation in Excel
Data validation is a feature in MS Excel, which is used to control what a user can enter in a cell of 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.
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.
- Select on Data Validation from the toolbar under the Data Tab:
How to Create Data Validation Rule in Excel?
Let understand the working of Data validation in excel by some examples.
Excel Data Validation – Example #1
We can restrict a user to enter a whole number between 0 and 10.
Execute the Below mention steps for creation of the data validation rule in excel:
Step 1: Select B2 Cell.
Step 2: Go to the Data tab, Click on Data Validation from Data the toolbar.
Step 3: A data validation Pop-Up will open:
Step 3.1: On the Settings tab, Click on Allow drop-down under validation Criteria.
Step 3.2: Select Whole number, then Some more required options will be enabled.
Step 3.3: Select between from the drop-down of Data list, Enter the Minimum and Maximum number for restriction. And click ok.
Step 3.4: All setting 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.
Now Enter 11 in cell B2- It will throw by default error.
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.
Step 2: Click on the data validation tool from the Data Menu Bar and select Text Length in Allow drop-down.
Step 3: Select equal to from the data list drop-down.
Step 4: Enter your desired length of the password (as an example 11).
Step 5: Click on the Input Message tab, provide a message which will be displayed on the selection of cell.
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.
Step 7: After that click Ok button, the setting will be applied to the selected cell.
On the Selection of Cell, Input message will be displayed.
Enter a text which has a text length of 11. It will be selected successfully.
Now enter an invalid password. It will throw the described error Message.
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.
Step 2: Select custom in Allow drop-down.
Step 3: Write a formula for selecting only value if it is having ‘@’.
Step 4: Click on the Input Message tab, provide the message which will be displayed on the selection of a cell.
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.
Step 6: After that click Ok button, the setting will be applied to the selected cell.
On the Selection of Cell, Input message will display.
Enter a valid e-mail id – It will be selected successfully.
Now enter any invalid e-mail Id- It will throw a described error Message.
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 remaining cells in the sheet.
When to Use Data Validation in Excel?
- Best used when a user wants to share a sheet with other a 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 data validation rule in the workbook for excel web apps or Excel services, then he needs to create excel data validation rule in desktop first.
- If one a user sender workbook to another then first a user needs to make sure that workbook is unlocking otherwise second a user 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 can create 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 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 downloadable excel template. You may also look at these useful functions in excel –
