EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tools Excel Data Validation
Secondary Sidebar
Excel Functions
  • Excel Tools
    • Excel Shortcut Redo
    • Reduce Excel File Size
    • Quick Analysis in Excel
    • Goal Seek in Excel
    • Compare Two Lists in Excel
    • Excel Quick Analysis
    • Estimate Template in Excel
    • Pivot Table Count Unique
    • CSV Files into Excel
    • Excel Business Plan Template
    • Excel Export to PDF
    • Free Excel Template
    • Excel Repair
    • Color in Excel
    • Form Controls in Excel
    • Timecard Template in Excel
    • How to Unhide All Sheets in Excel?
    • Power Query in Excel
    • Power View in Excel
    • XML in Excel
    • Excel Evaluate Formula
    • Examples of Excel Macros
    • Consolidation in Excel
    • Ribbon in Excel
    • Excel Conditional Formatting for Dates
    • Protect Sheet in Excel
    • Data Model in Excel
    • Pivot Table Examples
    • Pivot Table Slicer
    • Pivot Table Filter
    • Watch Window in Excel
    • Slicer in Excel
    • Print Gridlines in Excel
    • Convert Numbers to Text in Excel
    • Freeze Columns in Excel
    • Spelling Check in Excel
    • Name Box in Excel
    • Solve Equation in Excel
    • Excel Spreadsheet Examples
    • What If Analysis in Excel
    • How to Print Labels From Excel
    • Excel Named Range
    • Excel TRANSPOSE Formula
    • Excel Merge and Center
    • Excel Freeze Rows
    • HLOOKUP Formula in Excel
    • Excel Create Database
    • Excel Gridlines
    • Excel Spreadsheet Formulas
    • Excel Sort By Number
    • Excel Concatenating Columns
    • Excel AutoCorrect
    • Conditional Formatting For Blank Cells
    • Excel Icon Sets
    • Excel CTRL D
    • Excel Accounting Number Format
    • Excel Regression Analysis
    • Excel Import Data
    • Excel Freeze Panes
    • Excel Calendar
    • Excel Developer Tab
    • Excel Enable Macros
    • Excel Autosave
    • Heat Map in Excel
    • Excel Toolbar
    • Excel Error Bar
    • Excel Status Bar
    • Excel Unprotect Sheet
    • Check mark in Excel
    • Excel Column Filter
    • Excel Header and Footer
    • Excel Drawing
    • Range in Excel
    • Timeline in Excel
    • Excel Lock Formula
    • Excel Table styles
    • Insert New Worksheet in Excel
    • Excel Column Lock
    • Excel Forms for Data Entry
    • QUOTIENT in Excel
    • Excel Sorting
    • Excel Sort by color
    • Excel Data Bars
    • Excel Tool for Data Analysis
    • Excel Flash Fill
    • Excel Auto Fill
    • Excel Quick Access Toolbar
    • Excel Wrap Text
    • Excel Exponential Smoothing
    • Excel ANOVA
    • Excel Merge Two Tables
    • Excel Conditional Formatting in Pivot Table
    • Dynamic Tables in Excel
    • Excel Sort by date
    • Excel Dynamic Range
    • Record Macro in Excel
    • Two Variable Data Table in Excel
    • Merge Cells in Excel
    • One Variable Data Table in Excel
    • Excel Fill Handle
    • CheckBox in Excel
    • Excel Table
    • Excel Combo Box
    • Auto Format in Excel
    • Advanced Filter in Excel
    • Excel AutoFilter
    • Excel Data Filter
    • Excel Data Validation
    • Excel Radio Button
    • Data Table in Excel
    • Text to Columns in Excel
    • Excel List box
    • Excel Solver Tool
    • Scrollbar in Excel
  • Excel Functions (12+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL ADVANCED Training
  • EXCEL Training COURSE

Excel Data Validation

By Arun GuptaArun Gupta

Data Validation in Excel

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 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.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,325 ratings)

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
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel Training (23 Courses, 9+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

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

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Data Validation Excel Template

EDUCBA

Download Data Validation Excel Template

EDUCBA

डाउनलोड Data Validation Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more