Data Validation in Data Analysis

Data validation is useful in data analysis when we need to make sure that the data entered in a worksheet is correct, consistent, and in the required format. It helps us control what users can type into a cell, so that wrong or unwanted values can be stopped at the time of entry. In Excel, it is used to handle employee forms, student marks, order sheets, attendance records, inventory lists, and other data entry sheets.

In this article, we will learn about data validation in data analysis, its uses, and some commonly used Excel data validation rules.

What Is Data Validation in Data Analysis?

Data validation is an Excel feature that restricts the type of data or the values that can be entered in a cell. We can set a rule for a cell or a range, and Excel checks every entry against that rule. If the entry does not follow the rule, Excel can stop it or show a warning.

For example, if a cell is meant for age, we can allow only whole numbers between 18 and 60. Similarly, we can create a drop-down list for the Department column, so that users can select a value instead of typing it.

Sample Dataset Used in This Article

We will use the following dataset in the examples. The headings are in row 1, and the entry range is A2:

Data Validation Types in Excel

Excel provides several validation types that are useful for data analysis. Some commonly used ones are:

How to Apply Data Validation in Excel

The basic process is the same for all validation types.

Steps

1. Select the cell or range where we want to apply the rule. 
2. Go to the Data tab. 
3. Click on Data Validation in the Data Tools group. 
4. In the Settings tab, choose the validation type and set the condition. 
5. Add an input message and an error alert if needed. 
6. Click OK. 

Whole Number Validation

The Whole Number validation is used to allow only whole numbers that meet a condition. It is useful for age, quantity, and marks.

Steps

1. Select the age range D2:D6. 
2. Go to Data > Data Validation. 
3. In the Allow box, select Whole number. 
4. In the Data box, select between. 
5. Enter 18 as the minimum and 60 as the maximum, and click OK. 

Output:
If we type 25 in the Age column, Excel accepts it. If we type 70 or 17.5, Excel shows an error message and rejects the entry.

Explanation:
In the above example, we select the Age range. After that, we choose Whole number and set the limits from 18 to 60. Finally, Excel allows only whole numbers within this range and blocks all other entries.

Decimal Validation

Decimal validation is used to allow numbers with decimal values. It is useful for price, percentage, rating, and weight. The setting steps are the same as Whole Number, but we select Decimal in the Allow box. For example, we can allow only values between 0 and 5 for a rating column, so a value like 4.5 is accepted and a value like 6.2 is rejected.

List Validation (Drop-Down List)

The List validation is used to create a drop-down list in a cell. Users can select a value from the list instead of typing it, which keeps the data consistent.

Creating a List by Typing Values

Steps

1. Select the Department range C2:C6. 
2. Go to Data > Data Validation. 
3. In the Allow box, select List. 
4. In the Source box, type Sales,HR,IT,Finance. 
5. Make sure In-cell dropdown is checked, and click OK. 

Output:
Each cell in the Department column shows a drop-down arrow with four options: Sales, HR, IT, and Finance.

Explanation:
In the above example, we select the Department range. After that, we choose List and type the allowed values separated by commas. Finally, Excel shows a drop-down arrow, and users can pick only from these values.

Creating a List from a Cell Range

If the list is long or may change, we can store the values in a separate range, such as H2:H5, and select that range in the Source box. We can also convert the list into an Excel Table or use a named range, so the drop-down updates automatically when new values are added.

Dependent Drop-Down List

A dependent drop-down list changes its options based on the value selected in another cell. For example, if we select a country in one cell, the next cell shows only the cities of that country. It is created by naming each list with the same name as the first selection and using the INDIRECT function in the Source box.

=INDIRECT(F2)

Here, F2 is the cell that contains the first selection.

Date Validation

The Date validation is used to allow only dates that meet a condition. It is useful for joining dates, due dates, and delivery dates.

Steps

1. Select the Joining Date range E2:E6. 
2. Go to Data > Data Validation. 
3. In the Allow box, select Date. 
4. In the Data box, select greater than or equal to. 
5. Enter 01-01-2020 as the start date, and click OK. 

Output:
Excel accepts a date like 15-06-2024 and rejects a date like 10-05-2018.

Explanation:
In the above example, we select the Joining Date range. After that, we choose Date and set the condition as greater than or equal to 01-01-2020. Finally, Excel allows only dates on or after this date.

Time Validation

The Time validation works in the same way as Date validation, but it checks time values. For example, we can allow only time entries between 9:00 AM and 6:00 PM for a shift timing column.

Text Length Validation

The Text Length validation is used to limit the number of characters in a cell. It is useful for mobile numbers, PIN codes, and employee IDs.

Steps

1. Select the Employee ID range A2:A6. 
2. Go to Data > Data Validation. 
3. In the Allow box, select Text length. 
4. In the Data box, select equal to. 
5. Enter 6 as the length, and click OK. 

Output:
Excel accepts EMP106 because it has six characters, and rejects EMP12 or EMP1067.

Explanation:
In the above example, we select the Employee ID range. After that, we choose Text length and set it equal to 6. Finally, Excel allows only entries that contain exactly six characters.

Custom Validation Using Formulas

The Custom validation gives us full control. It uses a formula that returns TRUE or FALSE. Excel accepts the entry when the result is TRUE and rejects it when the result is FALSE.

Steps

1. Select the range where we want to apply the rule. 
2. Go to Data > Data Validation. 
3. In the Allow box, select Custom. 
4. Enter the formula in the Formula box, and click OK. 

Example 1: Prevent Duplicate Entries

=COUNTIF($A$2:$A$100,A2)=1

Output:
If we select A2:A100 and apply this rule, Excel rejects an Employee ID that already exists in the range.

Explanation:
In the above example, we use the COUNTIF function to count how many times the entered value appears in the range. After that, the formula checks whether the count is equal to 1. Finally, Excel allows the entry only when the value is unique.

Example 2: Allow Only Text

=ISTEXT(B2)

Output:
Excel accepts a name like Amit and rejects a number like 1234.

Explanation:
In the above example, we use the ISTEXT function to check the entered value. After that, the formula returns TRUE only for text. Finally, Excel blocks any entry that is not text.

Example 3: Allow Only Weekdays

=WEEKDAY(E2,2)<6

Output:
Excel accepts a date that falls from Monday to Friday and rejects a Saturday or Sunday date.

Explanation:
In the above example, we use the WEEKDAY function with 2 as the return type, so Monday is 1 and Sunday is 7. After that, the formula checks whether the number is less than 6. Finally, Excel allows only dates that fall on working days.

Input Message

The input message is a small note that appears when a user selects a validation cell. It tells the user what kind of data is expected.

Steps

1. Open Data Validation for the selected range. 
2. Go to the Input Message tab. 
3. Make sure the “Show input message when cell is selected” is checked. 
4. Enter a title, such as “Age,” and a message, such as Enter a whole number between 18 and 60. 
5. Click OK. 

Explanation:
Whenever a user clicks the cell, Excel shows this message beside it. It reduces mistakes because the user knows the rule before typing.

Error Alert

The Error Alert is the message shown when a user enters invalid data. Excel provides three styles.

Steps

1. Open Data Validation for the selected range. 
2. Go to the Error Alert tab. 
3. Make sure Show error alert after invalid data is entered is checked. 
4. Choose a style, enter a title and an error message, and click OK. 

Explanation:
The Stop style is the strictest and is best for data that must always follow a rule. The Warning and Information styles are useful when exceptions are sometimes needed.

Circle Invalid Data

Data validation does not check data that was already in the cell before the rule was applied, and it does not check pasted values. The Circle Invalid Data option is used to find such entries.

Steps

1. Go to Data > Data Validation dropdown arrow. 
2. Click on Circle Invalid Data. 

Output:
Excel draws a red circle around every cell whose value does not follow its validation rule.

Explanation:
In the above example, we use Circle Invalid Data on a sheet that already contains values. After that, Excel compares each value with the rule of its cell. Finally, it circles the entries that are wrong, so we can correct them. To remove the circles, choose Clear Validation Circles from the same menu.

Editing and Removing Data Validation

To edit a rule, select the cell, open Data Validation, change the settings, and click OK. If we want the change to apply to every cell with the same rule, check the Apply these changes to all other cells with the same settings box.

To remove a rule, select the cells, open Data Validation, and click on Clear All. To select all cells that have validation, go to Home > Find & Select > Data Validation.