Excel data validation is critical to spreadsheet management, ensuring accuracy and reliability. You can impose precise guidelines using it. If users attempt to submit inaccurate data, it also enables users to display a customized notice.
Data validation is the hero in a world where everyone’s obsessed with data, from bean counters to big shots. It differentiates between using spreadsheets as a beginner and masterfully managing data.
In this post, we’ll explore the world of data validation in Excel to learn its significance and how it enables you to keep error-free data easily.
What is Excel Data Validation?
You may manage the type of data entered into the worksheet with the help of Excel’s data validation feature. For instance, Excel data validation enables you to restrict some data entries, such as dates or numbers, and to limit data entries to a selection from a dropdown list.
It’s a useful tool, particularly when distributing an Excel worksheet, essentially for completion by others. When working with multiple users or strict guidelines for data entry, many data analysts agree that Excel’s data validation feature is helpful.
Excel’s built-in data validation feature can assist analysts in avoiding wasting time and money on incorrect input.
The Limitations of Data Validation in Excel
- Excel data validation sets limits but is simple, resulting in mixed-up data.
- Data collection is frequently uncontrolled, leading to disorganized spreadsheets.
- For accurate analysis, analysts frequently need to clean and manage data.
- Intelligent data preparation technologies provide data validation that is quicker and more precise.
- Data preparation tools shorten and simplify the analysis process.
- Excel data validation is still important, but it can necessitate additional data preparation.
- Data preparation includes eliminating duplicates, standardizing data, and removing null values.
- The data preparation improves the efficiency of Excel data validation.
What Does the Data Validation Feature Do?
Settings Tab
The validation criteria are entered on the settings tab. There are eight techniques for user input validation:
- Any Value: This eliminates any previous data validation.
- Whole Number: Only whole numbers are permitted. You may agree that the user must enter a value between 0 and 30.
- Decimal: The user must provide a decimal-valued number.
- List: The user must design a dropdown list from which to choose.
- Date: The format of the date must be entered by the user.
- Time: The user needs to enter a time.
- Text Length: Based on the size of the input, it verifies it.
- Custom: A special formula is used to verify user input.
Input Message Tab
The input message might specify the data that can be entered in a particular cell. This tab is not required.
- Show input message when the cell is chosen should be checked.
- Add a title.
- Enter a message here.
Error Alert Tab
You can display an error message if a user attempts to enter invalid data.
On the tab for error messages:
- Select “Show error alert after invalid data is entered” in the dropdown menu.
- Add a title.
- Enter a message of error.
Types of Data Validation in Excel
There are 8 types of data validation you can use in Excel, which are as follows:
- Any Value: This eliminates any previous data validation.
- Whole Number: Only whole numbers are permitted. You may agree that the user must enter a value between 0 and 30.
- Decimal: The user must provide a decimal-valued number.
- List: The user must design a dropdown list from which to choose.
- Date: The format of the date must be entered by the user.
- Time: The user needs to enter a time.
- Text Length: Based on the size of the input, it verifies it.
- Custom: A special formula is used to verify user input.
Let’s discuss how to use a list as a data validation in Excel.
How to Use List in Data Validation in Excel?
Here are a few steps to learn how to validate data in Excel:
Step 1: Choose the cell that has to be validated. The Data Validation button can be found by selecting Data > Data Tools.
Step 2: On the Settings tab, under Allow, select an option from the given below:
- Whole Number
- Decimal
- List
- Date
- Time
- Text Length
- Custom
Step 3: Select fields as shown in the image below and select cells containing a list of values in “Source”. You should also check “Ignore blank”, if you want to allow blank values as well.
Step 4: Excel also allows us to show a message to the user when selecting a value from drop down. A message that users will view when inputting data can be modified by choosing the Input Message tab.
Step 5: To display the message when the user picks over the selected cells, click the Show input message when the cell is selected checkbox, as shown in the image above.
Step 6: Choose a Style and modify the error message by selecting the Error Alert tab.
Step 7: Click OK.
If you write the wrong input, then an error occurs, and the system gives you a warning so that you avoid invalid input, as shown in the image below.
How to Copy Excel Data Validation Rule to Other Cells?
Follow these four simple steps for copying the validation rule in Excel:
- Press Ctrl + C to copy the cell to the validation rule.
- Choose the additional cells you want to verify. Press and hold the Ctrl key when choosing the cells to choose non-adjacent ones.
- Select Validation from the Paste Special menu after right-clicking the selection.
- Alternately, use Ctrl + Alt + V, then N to access Paste Special > Validation quickly.
- Select OK.
How to Find Cells with Data Validation in Excel?
Go to the Home tab > Editing group and select Find & Select > Data Validation to find all validated cells in the current worksheet quickly.
It will choose all cells for which any data validation criteria have been applied, as shown in the image below.
Conclusion
Excel’s data validation feature gives users an interface to collect accurate data while limiting human errors. Standard data entry and organized reporting are the results.
In detail, this article has concluded: what is data validation in Excel?
With over two decades of experience in writing about Microsoft Excel, Google Sheets, and various other spreadsheet tools, Muhammad Nadeem Salam is your go-to expert for all things data. Since 2004, he has been passionately sharing his knowledge and insights through engaging and informative blog posts, helping countless readers unlock the full potential of their spreadsheet tools.
One thought on “What is Data validation in Excel? How to Add, Use & Remove it?”