Microsoft Excel Deadly Sins 8: Excel Data Entry Validation

Microsoft Excel Data Entry Validation

8 Do not leave data entry unvalidate

Huh? Why do you have to validate data entry? You can never deny one true fact, which is near-universal of corporate uses Excel to record simple day to day transaction. Many of them keep as many as 34,000 of spreadsheets but fail to keep these records validated. You will find many errors and inconsistent data being entered to the spreadsheets, which causing many tools in Excel couldn’t work or perform properly. It is not only that, many times the analysis might not accurate being all data are left unvalidated.

For most of the time, data entry might could involve the effort from a few people. And the next thing you realize these people will have different way of working with Excel spreadsheet. Give you a simple example, entering phone number into the Phone# field, situation shows in the below figure could happened.

Simple data entry, enter by 3 different people.

Microsoft Excel Deadly Sins 8: Excel Data Entry Validation - Info Trek

The phone number is not found in different format. Person A decided to place the country code as the prefix for the phone number; Person B decided to enter the date with dash “-“ and then spacing after the first 4 digit; Person C simply enter the phone number only. Finally, when we were to combine these 3 records in one, it is going to be difficult to analyse. Proper planning and keeping everyone sync is a very important step before any of these begins, if not, these data entry should be done using a validated template. All crew member who’s going to work on the template will end up with dataset that are in the same formatting.

Another example could lead to serious analysis problem will be the State field. For example, Kuala Lumpur. Some may refer it as K.L., some may refer it as Wilayah Persekutuan. As you can foresee, the dataset end result is going to be very inconsistent like what shown in the figure below.

Microsoft Excel Data Entry Validation - Info Trek

So, you have not realize the problem aren’t you? Until the time where you are going the analyse the data, it is going to be the pain in the ass. Let’s say you would like to find out, how many of the contact person is from the state Kuala Lumpur. Let’s say, you are going to analyse using the function COUNTIF(). This is the argument for COUNTIF() function.

What will you be entering into the criteria field? “Kuala Lumpur” ?

Or “Wilayah Persekutuan” ?

With either of the entry, you answer is not going to be correct and you are going to end up with an inaccurate analysis.

The solution is very simple, Data Validation. With the combination of Data Validation tools and Table object (Check out the Deadly sins part 9 to check out how Table object works) in Excel your life is going to be a lot easier. With this, your analysis will be 99.99% accurate.

Case No. 1: Use whole number to validate the phone number.

Before you start validating the field, you will have to make sure what is the use of colleting those data. For example, you the data that you’re going to collect is simply the phone number from the same country, then don’t bother to record the phone number country code. But if the data that you’re going to collect from is people from around the world, and you will want to analyse them base on the country, then make sure the phone number entry include with the country code. It is a good start if you plan ahead, so you will know what are the information you’ll need to collect.

Okay, let’s say, the information that I’m going to collect is simply from the people within Malaysia, which means my analysis will not involve the origin of those people. The data entry for the phone number will not need to include country code. For this case, using whole number setting, you will be able to validate the field.

For Malaysia mobile phone number are ranging from 7 to 8 digits. So, the validation will be ranging the value from 1,000,000 to 99,999,999. The example will be as below:

If the amount of digit enter is less than 1,000,000 or more than 99,999,999, error will be prompted.

Case No. 2: Use list to validate the State field

If the entry is being fixed by a list of items, then you should validate the field using List. For example, month. The month entry will no be other than Jan – Dec, so List will be one of the best validating selection.

In this case, we’re going to validate the State field using List.