Microsoft Excel is a powerful tool and is widely usefed just because MS Excel offer various features to ease our work. One such feature is data validation. Now suppose you want the user to enter some specific values into the cells, and for that, you need to set some predefined rules so that the user wouldn't be able to enter other values, and this is where data validation becomes one of Excel’s most valuable features. It helps control what users can enter into a cell—reducing errors, improving data quality, and saving time on cleanup.
In this data validation in Excel guide, you’ll learn how to apply different types of data validation, customize settings, and use practical examples to streamline your data entry process.
What is Data Validation in Excel
Data Validation gives you the control to receive particular inputs from users. We all have encountered using this feature in our day-to-day lives, one such example is while filling out forms in which the age cell will accept numbers, the name column accepts text with limited characters, and the date of birth will have years pre-defined to rule out the ineligible candidates.
How to do Data Validation in Excel
The data validation function can be found in the DATA tab from the Exceln (as seen in the picture below).
Step 1: Click on the Data Tab in the Ribbon.
Step 2: Now select Data Validation.
.png)
In seAfter clicking on Data Validation, a menu appears.
Step 3: Select Data Validation and a dialogue box appear.
There are 3 tabs in the dialogue box.
- Settings: This will help you to select the data type and the type of data that you want to be filled in the desired row or column.
- Input Message: This tab will help to let the user know about the constraints you've decided on for the row/column. It will alert the user to input the right set of values.
- Error Alert: The error alert tab will help the user to know that they had entered invalid data.
Note: The data validation feature is not 100 percent reliable. If you will try to copy the data from cells which has no defined validation rules and then try to paste those cells to cells having data validation then all the validation part get vanished. Basically, validation rules get changed from the corresponding cell based on the copied cell content.
Settings Tab
In the settings tab, you can find options to set validation criteria. The Setting Tab helps us to choose the validation rule as per your need from the in-built options. We also have the option to select custom rules with the customized formula for user inputs. There are all the options for Data Validation in the settings tab.
.png)
The Input Message Text tab has a Text box to enter a message displayed as soon as the user selects the respective cell to enter the data.
If you want to display a message that helps the user to understand what type of data is allowed to enter in the data in the given cell.
Follow the below steps:
Step 1: Open the Input Message Tab.
Step 2: Make sure there should be a tick mark on the "When a cell is selected, show input message".
Step 3: Enter the Title and Text of your message in the fields.
Step 4: As soon as the user selects the Validated cell, It will show this message.
.png)
Error alert Tab (Optional)
The error alert tab helps us to provide the option to control how the validation is enforced. We can apply different criteria and then use any desired error style according to the user input. We can also display a message to the user informing them about the type of error and what values must be entered in the given cells.
There are three types of error styles in Excel
If you want to configure a custom error alert message then follow the steps below:
Step 1: Click on the Error Alert in the Data Validation
Step 2: Make sure there is a tick mark in the box "Show error alert after invalid data in Entered".
Step 3: You can Select the desired error style in the Style box.
Step 4: Enter the title and text of the error message and click "ok".
.png)
Excel Data Validation List
Data Validation also provides a feature of adding a drop-down list to a cell or group of cells.
Follow the below steps to add a drop-down list:
Step 1: Select a cell in which you want to add the drop-down list.
Step 2: On the setting tab, Enter List in the Allow box.
Step 3: Type the Items of your Excel Validation list in the Source Box, Separated by commas For example To limit the user input into two choices type Yes, NO.
Step 4: Now select the In-cell dropdown box in order for the drop-down arrow to appear next to the cell.
Step 5: Press "OK"
.png)
Example of Data Validation
Let's take the example of filling out a form. The form requires your name which has a limitation of 3-7 characters, it requires your date of birth, and has a list of cities for the exam center. Not considering all the other requirements as of now.
The form looks like this.
To apply data validation with a word limit of 3-7 characters for the Name cell.
Step 1: Select the empty cell in front of the Name.
Step 2: From the DATA tab in the ribbon, select Data Validation.
Step 3: A Dialogue box will appear.
Step 4: In the dialogue box from the setting tab, in the dropdown, select Text Length (as shown in the image below).
Step 5: We want our user to enter the name between 3-7 characters, So in the Minimum column we'll write 3 and in the Maximum column we'll write 7 and then click OK.
The Name row will now accept only text between 3-7 characters.
To use Data Validation as Date of Birth
Step 6: Select the cell in front of Data of Birth in Excel.
Step 7: Repeat steps 2 and 3.
Step 8: In this step, instead of selecting text length, you need to select Date (as shown in the image below).
If you want the user must be born between 1st January 2000 to 1st January 2021. Enter the Start date as 1st January 2000 and the End date as 1st January 2021.
Step 9: Click OK.
Now, the Date of Birth row will accept dates between 1st January 2000 to 1st January 2021.
Excel Data Validation List (Dropdown)
Step 10: Select the empty cell in front of the Exam Centre.
Step 11: Repeat steps 2 and 3.
Step 12: Select List (as shown in the image below).
You want to add "Kanpur", "Agra", "Aligarh", "Lucknow", "Varanasi" to the list.
Step 13: Add the names in the source column separated by a comma(,).
Step 14: Click OK.
The Exam center cell will look like this.
You've successfully created a form with 3 requirements using Data Validation.
Date Validation
Date Validation can be set -up in Excel. Select date in the allow box and pick up the appropriate criteria.
Follow the below steps to set-up date validation:
Step 1: Select the cell
Select the cell where you'd be applying the data validation
Step 2:
Similar Reads
Non-linear Components In electrical circuits, Non-linear Components are electronic devices that need an external power source to operate actively. Non-Linear Components are those that are changed with respect to the voltage and current. Elements that do not follow ohm's law are called Non-linear Components. Non-linear Co
11 min read
Spring Boot Tutorial Spring Boot is a Java framework that makes it easier to create and run Java applications. It simplifies the configuration and setup process, allowing developers to focus more on writing code for their applications. This Spring Boot Tutorial is a comprehensive guide that covers both basic and advance
10 min read
Class Diagram | Unified Modeling Language (UML) A UML class diagram is a visual tool that represents the structure of a system by showing its classes, attributes, methods, and the relationships between them. It helps everyone involved in a project—like developers and designers—understand how the system is organized and how its components interact
12 min read
Steady State Response In this article, we are going to discuss the steady-state response. We will see what is steady state response in Time domain analysis. We will then discuss some of the standard test signals used in finding the response of a response. We also discuss the first-order response for different signals. We
9 min read
Backpropagation in Neural Network Back Propagation is also known as "Backward Propagation of Errors" is a method used to train neural network . Its goal is to reduce the difference between the model’s predicted output and the actual output by adjusting the weights and biases in the network.It works iteratively to adjust weights and
9 min read
Polymorphism in Java Polymorphism in Java is one of the core concepts in object-oriented programming (OOP) that allows objects to behave differently based on their specific class type. The word polymorphism means having many forms, and it comes from the Greek words poly (many) and morph (forms), this means one entity ca
7 min read
3-Phase Inverter An inverter is a fundamental electrical device designed primarily for the conversion of direct current into alternating current . This versatile device , also known as a variable frequency drive , plays a vital role in a wide range of applications , including variable frequency drives and high power
13 min read
What is Vacuum Circuit Breaker? A vacuum circuit breaker is a type of breaker that utilizes a vacuum as the medium to extinguish electrical arcs. Within this circuit breaker, there is a vacuum interrupter that houses the stationary and mobile contacts in a permanently sealed enclosure. When the contacts are separated in a high vac
13 min read
AVL Tree Data Structure An AVL tree defined as a self-balancing Binary Search Tree (BST) where the difference between heights of left and right subtrees for any node cannot be more than one. The absolute difference between the heights of the left subtree and the right subtree for any node is known as the balance factor of
4 min read
CTE in SQL In SQL, a Common Table Expression (CTE) is an essential tool for simplifying complex queries and making them more readable. By defining temporary result sets that can be referenced multiple times, a CTE in SQL allows developers to break down complicated logic into manageable parts. CTEs help with hi
6 min read