Le

The more I work with customers, the more I realize that data quality is still a very big problem. Data Validation in Excel is an effective solution to this problem. Here is a short guide for you. Reading time 12 min.

The Need

Data must be entered accurately. That is a universal need. Correcting mistakes at the time of typing the data is the easiest and fastest method. Remember that there are three types of data: Numbers, text and dates. Within text, you may want to restrict typing and provide a dropdown.

How to apply Data Validation in Excel

Select the range where data is to be entered. Go to Data tab – Data Validation and choose the desired validation. The most powerful is the last one – Custom.

Default is any value. That is why Excel accepts any type of data in any cell. We have to make Excel behave more strictly by applying these validations. Whole number or decimal is for numbers. Date and time is also set the same way by giving the upper and/or lower limits.

Only the text length can be controlled. If you want to specify a predefined set of items to choose, use the List option. The list of valid options must be already created somewhere. Usually such sheets are hidden to prevent users from changing the base values.

Here is a detailed explanation on how to use each option.

Use Excel Table for list data

The list itself must be a formal Excel Table (Insert – Table). This way, any additions or deletions from the list are automatically reflected into the validation drop-downs.

How to apply Data Validation in Excel to multiple areas?

Simple Copy – Paste Format is enough. The formatting includes Data Validation settings as well. If you just want to paste validations, choose the appropriate option from the Paste Special dialog.

Remember to provide an input message

This is the commonest mistake I have observed. Why did we put the validation in the first place? To capture data accurately- right? Now, look at it from the point of view of the person entering the data. That poor thing has no clue what validation you have applied. Wrong entry will give an error message. Most often the error message is also the default one – which does not say what is the right value! What is the point? It only leads to more frustration.

That is why, when you add validation, there is an Input Message tab. Which allows you to type the correct values for the data. So here is the best practice. As soon as you add validation settings, change the input message immediately.

          

Unfortunately, you have to manually type the message.

You cannot make data entry mandatory

This is another common question. No. Without programming, there is no way to make data entry into specific cells mandatory in Excel. Data Validation does not help. You can clear the Ignore Blank checkbox to make data entry more strict. It works only if you go to a cell and press ENTER. At that point, it will force you to enter correct data. However, if you never go to a cell and press Enter the validation is simply not triggered.

Applying validation AFTER data is entered

Data Validation in Excel can be applied post-facto. This is required when you already have the data but you want to check it quickly based upon various validations.

What happens if the data fails validation? Excel will not delete it, rather, it cannot delete it.

Then how do you know which data is valid and which one is invalid? Two methods available. First one – the older method – is to apply Validation Circles – something which your finance team would fall in love with!

NO need to select the data. These circles will highlight any data which is failing the applied validation across the sheet. These circles do not get printed. When you change the data as per the expected validation rule, the circle goes away.

       

 

Of course, this option needs to be selected manually. At this stage, you know what is invalid. But you don’t know what was the actual validation. If the sheet is protected, you cannot see the validation itself. If someone else has added these validations, you will not be able to see the validation condition.

But there is another simpler method. Just create a table around the data. Now you will see the famous Green error mark. This shows the expected validation range, without the need to go to the Data Validation dialog.

        

Best Practices for Data Validation in Excel

First of all, your data as well as lists for validation should be in tables.

Add useful and accurate input messages for all validations.

Remember to keep enough rows to accept data.

Select and unprotect the cells – Cell Format – Protection – Uncheck Locked

Protect the rest of the sheet

Do not hard code dates as far as possible. Use the Today() function to create validation rules. That way, the template will be valid for a longer period of time.

Disadvantages and the Solution

If multiple people are going to use the data entry template, you will need to receive their inputs and manually copy paste the entered data into a master sheet. That is a very cumbersome process.

Using a SharePoint list, this process can be very easily streamlined. We simply do not use Excel for data entry. We use SharePoint. See this video to understand how it is done.

[youtube https://youtu.be/KbdTCYARmj4]