When we talk about a data validation feature in excel then it is like we can explore our knowledge in excel in data validation. Data validation has many features to use which are highly user-friendly and easy to use. Here we will learn with live examples and get to know all the basic and advanced things in data validation. Data validation restricts users to make any unwanted changes in data which is validated by this using this data validation feature.
 |
| Data Validation |
We can also say that way as; When we control users putting any
values in the excel sheet then this feature of excel will be called data
validation. It is being used for checking the accuracy and quality of
source data before using, importing or otherwise
processing data. Various kinds of approval can be performed relying
upon goal imperatives or destinations. Information approval is a type of
information purifying. Let us learn Data Validation in detail here.
1). Open an excel sheet which consists of data.
2). Click the Data tab on the top of the ribbon.
Data Validation:- Once you click the data tab, You get these 3
options.
 |
| Data Validation Image |
Data Validation:- Once you click it and you will have these 3 options.
Settings - Input Message - Error Alert
 |
| Data Validation Features |
In the settings, you have ALLOW which gives the option to select any cell in the excel sheet and put a data validation If you want that your selected cell needs to have only numbers and a condition must be there such as, We can put numbers from 1000 to 15000 or any and hit the OK button then data validation will be activated in selected cells then nobody can put numbers besides we activated or made a condition there.
NOTE – Data validation does not work in the case of copy and paste. For example:- In the selected cells no other numbers can be entered but if you or anybody put the numbers in excel, select an area of the data and copy from there to paste in the selected area then numbers will be entered there without any issues.
Let us check here now:-
 |
| Data Validation Function |
Copy and paste work will not be worked at all if it is done from a non-selected area in an excel sheet.
In the same way, we can put any condition or make data validation the weather in cells
for TEXT or NUMBERS Can be used for data
validation such as
- Any Value
- Whole number
- Decimal
- List Date
- Time text length
- Custom
Let us have a look here at the screenshot below.
 |
| Data Validation |
First, it shows ALLOW, That means whatever condition we put here in the data by using the feature of data validation, will work accordingly.
Any Value – It gives all the options to set a condition as per or need or choice. We need to simply select the data and work as per the feature that we select in the box of data validation. This feature of data validation, we already understood with the example above however let us come to the next one more example. Let us take another example here first to understand it in a better manner. We will work on another feature to set up a condition in the LIST in data validation.
 |
| Data Validation |
LIST – Here we are working on the LIST function, How it works, Let us check and learn in a better manner here.
First, we need to select the condition from the dialogue box as LIST and come to the next tab which is SOURCE in the dialogue box, Here we select the data where we want to put a condition as per the LIST tab.
It works in texts value If you select all the texts in ongoing then there you can see a list of names or texts which will come as a drop-down list. Then you can select any name or text value to put in the column. It will show you only those names or text that you have in the selected area.
Question:- Now there is another case that comes in, let's suppose you do not want anybody to make any changes in the text value or no editable things in it. Then how to do it?
Answer: - In this case, we do not have to select all the names or text values however, you need to put all names or text values in the dialogue box manually with a comma (,) and then hit OK.
Now it does not allow any other user to put any other text or anything in it, User has to put only the text that has been set up as a condition by this data validation feature.
With the same thing, Also we can put an error message to show that user which will ask the user not to put anything there except the validated values. This one is also an awesome feature in data validation. It needs to be done with another tab that we learned above, INPUT MESSAGE.
Question:- How to use input message?
Answer:- Input Message:- We can use it as if we want to put any writing a message in the cell when someone will try to type something in the cells which is already conditioned under data validation, will appear clearly,
It can be anything you type & will be displayed anyhow. OR Now, if the user tries to enter a value that is not valid, a pop-up appears with the message, “This value doesn’t match the data validation restrictions for this cell.”
ERROR ALERT:- It also can be used for putting any value or text such as STOP, WARNING, and INFORMATION in the selected area where we already put data validation, Will be appeared when someone tries to put any value there.
Whole
Numbers:-
This condition we already learned above, It is about setting up a condition to
put values in numbers between 2 numbers.
Decimal:- This feature provides the best thing as always, We can set up a condition in values
in decimal, It will not allow the user to put values except the one mentioned
in decimal, as we use it for whole numbers, In the same way, it works in decimal
too.
Date:- It works as
same in the date format, Whatever the format we fix or set up here as a
condition, it will only show that all users. It doesn’t allow the user to put
any other date format.
Text Length: - This the feature allows user t set up a condition for text length which means the text length
we put, it will allow only the one we put already as a condition. Example – If you
have put a condition to put only 7 letters in the name as maximum then it will
not allow anybody to put more than 7 letters in the name at all.
Time:- It only shows the time that we put as a condition like other features that we learned above.
