Data Validation in Excel

What is Data Validation in Excel?

Data is the collection of raw facts and figures, which requires proper formatting and adjustments to convert into information. Validation involves setting standards and procedures to carry out an activity to maintain the necessary degree of adherence.

Data validation assures data quality, accuracy, and consistency to meet the standards.

Validating data is used for restricting inputs to avoid data integrity. For example, let us take a case when you are working on an Excel sheet of an employee database, and you will limit the numeric data type in the names of the employee through validation, which will warn the user if they try to input numeric formats in the name of the employee column.

Similarly, the ID of the employee column should permit only numeric data types. So, we will restrict the column to the numeric data type only by validating the data.

The data validation feature facilitates you to

• Execute control on what input a user can inject into the cell and the information the user cannot enter.
• Notifies the user with warning messages explaining the rejection of the entry.
• Informs users in advance of the type of data that can be entered.
• Prevents the occurrence of errors, which ensures data consistency.
• Allows users to create a list of items that can be entered, necessarily a dropdown list.
• Eases data entry.

Understanding Data Validation In Excel

Data is a powerful resource that decides a business's fate in the technology and innovation era. Data is everywhere, be it blogs, images, articles, journals, newspapers, etc. But, the issues of quality, privacy, and integrity cannot be missed and must be addressed.

We know that data is a lifesaver for businesses and crucial for making decisions.

Data Validation permits you to monitor the activities undertaken and also to have an understanding of the health of the business. We will be looking at the tool or feature in Excel which addresses the issues with data.

Data Validation is a feature in Excel that is responsible for exerting control over the data inserted by the user as per the standards set by the owner.

It is remarkable to note that this tool is responsible for adherence of the inputs to the rules set. At the same time, it will help maintain data accuracy and quality in the initial stages of analysis.

Although many of the folks are running after learning popular tools such as macros, VBA, power queries, and modeling, it is critical to understand validation which makes the data reliable.

Importance Of Data Validation

It is imperative to have control over the data before it is sent for further processing, analyzing, visualizing, interpreting, and reporting to avoid the wastage of money, materials, man, and methods.

Realizing this and working on the initial stages makes data reliable and helps make informed decisions. We will also look at the importance of validating the data in detail.

1. Ensures Data Accuracy
Accuracy is defined as the process followed that results in meeting the standards set. The validation feature is designed to ensure that the data inputted will meet the requirements set by the user. Accurate data is critical to arriving at potential decisions, as discussed earlier.

2. Ensures Data Consistency
Consistency means maintaining uniformity of the data across various applications by leveraging the information. With solid character, the client working on the sheet was informed about the recent rules and updates.

If the updates don't meet the rules set through validation, then only the changes are reflected across the sheets.

3. Ensures Data Integrity
Compromising data integrity is risky because we would compromise on data accuracy, consistency, reliability, and completeness. Therefore, to avoid data leaks and data loss, integrity is ensured.

Integrity can be achieved by validating the data. We can also ensure that the data is never handled inappropriately, is never miscategorized, and is not prone to probable risks.

4. Prevention of Errors
Errors cause a great deal. However, some built-in functions within the tools in Excel prompt users not to commit. One such tool is validation. The automatic response to the wrong inputs and the reason for false information in the form of warning messages make this tool popular.

5. Decision Making
In the end, what the organization does is that they make decisions. Therefore, the information must be analyzed and processed carefully before making decisions.

For example, suppose a business is looking forward to expanding internationally. In that case, it collects information on the customer's requirements, geographical data, perception of its brand, and competitor strategies, then analyzes the information and decides whether to expand.

Types Of Data Validation

Diving deep, let's understand the types to ensure correctness. Then, based on the different characteristics data possesses, we will validate by understanding the data first and then moving forward to set up our criteria to meet the requirements.

1. Based on data Type
There are different data types in Excel, such as whole numbers, decimal numbers, boolean expressions, date formats, text or string, currency, etc. These data types regulate the operations that can be done to a cell.

The owner typically will set the rules to enter a particular data type into a defined range or cell; if regulations are crossed, a warning message pops up and prompts rejection.

2. Checking your code and cross-reference
Renowned institutions set some standards with some assumptions which are mandatory to follow. In addition, there are some constraints imposed to comply.

For example, while working on the employee database of an organization, the phone number should have a standard limit of 13 characters, including the country code. This is an external standard set and mandatory to comply with.

3. Checking consistency
Consistency deals with a logical pattern of data. Therefore, validating the data will help maintain consistency. For example, if an exam was conducted for 50 marks and the data entered is 52, which is illogical and impossible, a consistency question is raised.

4. Checking uniqueness
Uniqueness reduces the repetition of entries. There will be many situations when working with databases where at least one field should be unique to understand the data. For example, the employee ID should be unique, and phone numbers should always be special.

5. Checking format
Format checking will allow only the values that meet the format set. For example, the owner might set the data only in dd/mm/yy format. So, if this format is selected, all the other forms, such as mm/dd/yy or yy/mm/dd, will be rejected.

6. Checking the blanks
This involves identifying if the user is allowing blank cells and warning them not to allow them. A warning will be issued if the cell is left blank, restricting further activity.

Until and unless the cell is filled, only Excel will start working.

An example of this is while working on an employee database, the name or ID of the employee should be filled in. So, validating data will remove blank cells.

7. Checking the Range
The range is the spread of cells from a minimum to a maximum value. In Excel, it is up to the owner to decide the content. Validating the range will input only characters that meet the conditions.

For example, the salary of an analyst should be between 50,000 to 60,000 in an employee database. So, the range of analyst salaries will only input values between 50,000 and 60,000.

In short, what validation makes sure of is the following:

• Only text or numeric data is allowed into a cell.
• Only numbers are permitted within a range.
• Look after the length of the characters that can be allowed.
• Detects incorrect data entered.
• Rejects the values outside the range.
• Prompts users with warnings and error messages.

So how do you apply Data Validation in Excel?

Let's look at the simple steps that should be followed to apply validation. We will also see the tool's presence and some shortcut keys to validate data.

Step 1: Finding the tool

Well, we are dealing with data, so it is obvious that the tool is included in the DATA tab of the ribbon bar. Under the data tools within the data tab, we can find the validation tool.

Step 2: We will work on validation now that we have seen where the feature was included. Click on data validation; we will be prompted with a dialogue box, as seen in the image below.

Step 3: Set the rules

The setting tab allows us to create and set up our criteria. For example, we can include values, formulas, and other column references.

For example, I would like to create criteria restricting the entry of values between 200 and 300. Therefore, I will set the minimum as 200 and the maximum as 300 in the validation criteria.

Step 4: Click OK to complete validation or move to input message, to warn the users. Working with the input message tab is optional.

Step 5: Go to the Input Message tab and see whether the "Show input message when the cell is selected "is checked if you mark it on.

Under the title, type in what you want to say to the users. Typically, the column name.

Under the input message, describe what is and is not allowed.

Click ok to complete the action. We will look at the result in the picture below.

Step 6: Optional error alerts

Different error alerts were set during validation, reflecting when the user entered invalid data.

The stop warning set by default will stop the functioning of Excel until and unless the value entered meets the condition set. Click on retry to reenter the discounts and for connections.

The warning error alert rejects the invalid data. This is a warning for users that you entered invalid data and enquires the user to continue the action or not. This error alert will not prevent users from the input. Continue to edit the entry to make it valid or cancel the admission.

The third error alert is information. This informs users that the value entered is invalid. This also doesn't prevent users from entering invalid data. Click OK to accept the invalid data "cancel" to cancel the entry.

The owner will set the error alert. Typically, a stop error alert is selected by default. But can choose the two other alert types from the style option.

Example of Data Validation

We will look at the example in detail to get more context.

Before going to an example, we will look at the data types allowed. Under the settings tab, we will have options to select validation criteria.

Selecting Data Type

The tool incorporates specific formats of data to ensure data security. Let us look at them below:

• Any value- Any numeric format.
• Whole numbers - Only whole numbers, i.e., all the natural numbers, including zero.
• Decimal - This includes whole numbers with decimal values.
• Date - Allows dates with specified start and end dates.
• Text length - allows you to set the size of the characters that can be entered.
• List-Customized lists(dropdown) can be created.

Selecting Data

This involves selecting the operators. The operators included are:

Greater than, less than, greater than or equal to, less than or equal to, between, not between, equal to, and not equal to.

Depending on the condition, limit the data by using the operations above.

Example

Condition( set by owner): The text length of the customer ID is limited to 5 characters.

Step 1: Select the cell or column for validating the data. Per the problem statement, I want to validate the customer ID.

Step 2: Go to the Data Tab, and select data validation. A dialogue box will appear. The shortcut key to activate it is to press Alt >D> L separately.

Step 3: On the dialogue box, go through settings first to set validation criteria. Under the allow dropdown select the text length option to limit the character's length. Limit the data to equal possibility. For example, select the length of 5.

Step 4: Enter the optional input message. Input the text into the title and the message you want to imply.

Step 5: Select the error type you want to display. This is also optional. I would use a stop error type to restrict the data entry. That's it. You have learned it.

How do I insert the data validation dropdown list in excel?

• Data>> Data Validation
• settings>> allow>> list
• Make sure In cell drop down and ignore blank is on 
• Under source, either enter the list of options or select the data from other cells through the up arrow at the end. I have created a dropdown list of three options, true, false, and N/A, which will allow the user to select only one of them.

Custom data validation in Excel

This is a type of validation that allows users to enter data in the way they want. For example, to meet our criteria set, we will be entering formulas. The formula will result in TRUE or FALSE, so if the requirements are met, TRUE will be displayed, i.e., the formula is executed.

Let's take an example to understand better.

Criteria: consider only textual data for names for the data given below.

• Data>> Data Validation or alt> D> L separately.
• Settings>> Validation Criteria>> Allow>> Custom
• Formula>> =ISTEXT(B2). Formula to allow textual data only.
• Click Ok.

An error alert will be displayed if any other data type is entered.

Limitations of Data Validation

Every tool at one or other point in time will face some limitations. Due to the data's complexities, the limitations were identified, and new tools were designed to overcome the obstacles.

1. Possibility of Errors: Systems are designed to prevent errors to a certain extent, but a hundred percent prevention of error is an impossible task. There are issues that, at times, excel may not be able to understand the condition and may result in errors. Therefore, we cannot say that we will always get error-free results.
2. Updations: One needs to revalidate data if there are any changes made. Therefore, we need to update the inputs and validation criteria to support validation. This is considered one of the most significant drawbacks of working with this feature.
3. Complicated Task: Labeled as a complicated feature, it is challenging to perform when dealing with multiple data sources. However, various tools available in the market will look after data.
4. Time Factor: Enterprises come up with tools to reduce the wastage of time as lost time equates to lost money. So, while there is an urgent need to complete data entry, we tend to ignore validating.

Researched and authored by Shivani Sukhavasi | LinkedIn

Reviewed and Edited by Raghav Dharmarajan

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• REPLACE Function
• Scatter Plot
• Secondary Axis
• Slicer in Excel
• Types of Graphs