Data validation is a feature in Excel which is used to control what users can enter into a cell. It allows you to dictate specific rules. It also allows users to display a custom message if users try to enter invalid data.

What Is Data Validation in Excel?

Data validation in Excel is a technique that restricts user input in a worksheet. It is often used to limit user entry.

data validation

Become an AI-powered Business Analyst

Purdue Post Graduate Program in Business AnalysisExplore Now
Become an AI-powered Business Analyst

Settings Tab

The settings tab is where you enter the validation criteria. There are eight options available to validate for user input:

  • Any Value - It removes any existing data validation.
  • Whole Number - It allows only whole numbers. For example, you can specify that the user must enter the number between 0 to 30.
  • Decimal -  The user must enter a number with decimal values.
  • List - The user will have to create a drop-down list to choose from.
  • Date - The user will have to enter the date format.
  • Time - The user should enter a time.
  • Text Length - It validates input based on the length of the data.
  • Custom - It validates the user input using a custom formula.

Input Message Tab

You can set the input message to explain what data is allowed in a given cell. This tab is optional.

  • Check the 'show input message when the cell is selected'.
  • Enter a title.
  • Enter an Input message.

step3

input output

Become The Highest-Paid Business Analysis Expert

With Business Analyst Master's ProgramExplore Now
Become The Highest-Paid Business Analysis Expert

Error Alert Tab

You can show the error message if the user tries to enter the invalid data. 

On the error message tab:

  • Check the ‘Show error alert after invalid data is entered’ box.
  • Enter a title.
  • Enter an error message.

step4

Now, when you try to enter the value beyond the range, you will get an error message.

output error

Now that we are familiar with the basic concepts, let’s look at a step-by-step process to implement data validation in excel.

How to Validate Data in Excel?

Step 1 - Select The Cell For Validation

Select the cell you want to validate. Go to the Data tab > Data tools, and click on the Data Validation button.

A data validation dialogue box will appear having 3 tabs - Settings, Input Message, and Error Alerts.

step1 data validation

Step 2 - Specify Validation Criteria

On the settings tab, specify your validation criteria. 

step2

Future-Proof Your AI/ML Career: Top Dos and Don'ts

Free Webinar | 5 Dec, Tuesday | 7 PM ISTRegister Now
Future-Proof Your AI/ML Career: Top Dos and Don'ts

Step 3 - Under Allow, Select The Criteria

Under Allow, select an option from Whole Number, Decimal, List, Date, Time, Text Length, and Custom. 

step3

Step 4 - Select Condition

Under Data, select a condition and set required values based on what you choose for Allow and Data.


step4

Step 5 - Input Message

You can enter the input message if you want. This step is optional.

Step 6 - Custom Error Message

You can also set your custom error message. This step is optional.

Step 7 - Click Ok

Click OK. Now, if you try entering a value outside the specified range (10, 50), it will result in an error

Become a Data Scientist With Real-World Experience

Data Scientist Master’s ProgramExplore Program
Become a Data Scientist With Real-World Experience

Excel Data Validation List (Drop-Down)

To add the drop-down list, follow the following steps:

  1. Open the data validation dialog box.
  2. On the Settings tab, select the list.
  3. In the source box, enter the list of your validation, separated by commas.

drop down input

You will get the following result.

drop down

Date Validation

You can set-up the date validation in Excel. Select date in the allow box and pick up the appropriate criteria. 

Follow these steps to set-up date validation:

  1. Select the cells where you’d be applying the data validation.
  2. From the allowed dropdown, select the date.
  3. From the Data drop-down, select between.
  4. Click in the Start Date box and select cell O10, where the Start Date is entered.
  5. Press the F4 key to change the cell reference to an absolute reference -- $O$10.
  6. Click in the End Date box and select cell O11, where the End Date is entered.
  7. Press the F4 key to change the cell reference to an absolute reference -- $O$11.
  8. Click OK to close the Data Validation window.

date data validation

The above date validation will only accept date values between 9th Nov 2020 to 18th Nov 2020. 

Kick-start your career growth story with PGP in Business Analysis. Get a chance to master Excel, Tableau, and Python tools. Start learning now!

Conclusion

The data validation in Excel provides a way of limiting the mistakes from the user and collecting valid data from the user. It results in standard data entry and systematic reporting. 

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel certification course, which includes Power BI training. You can even enroll for the Post Graduate Program in Business Analysis that helps you build extensive business expertise with real-world projects

The Business Analytics course is a comprehensive program that provides a very thorough understanding of the basic concepts of data analysis and statistics and how they help in data-driven decision making. The Business analytics training introduces you to Power BI, one of the most popular BI tools today, and allows you to learn the key statistical concepts to derive insights from the available data sets and help you present your findings using executive-level dashboards.

If you have any questions, do reach out to us by placing your queries in the comments section of this article (at the bottom). Our subject matter experts will promptly answer them for you. 

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Program NameDurationFees
Caltech Post Graduate Program in Data Science

Cohort Starts: 2 Apr, 2024

11 Months$ 4,500
Post Graduate Program in Data Science

Cohort Starts: 15 Apr, 2024

11 Months$ 4,199
Post Graduate Program in Data Analytics

Cohort Starts: 15 Apr, 2024

8 Months$ 3,749
Applied AI & Data Science

Cohort Starts: 16 Apr, 2024

3 Months$ 2,624
Data Analytics Bootcamp

Cohort Starts: 24 Jun, 2024

6 Months$ 8,500
Data Scientist11 Months$ 1,449
Data Analyst11 Months$ 1,449