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\u2019t work or perform properly. It is not only that, many times the analysis might not accurate being all data are left unvalidated.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Simple data entry, enter by 3 different people.<\/p>\n\n\n\n
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 \u201c-\u201c 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\u2019s going to work on the template will end up with dataset that are in the same formatting.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
So, you have not realize the problem aren\u2019t you? Until the time where you are going the analyse the data, it is going to be the pain in the ass. Let\u2019s say you would like to find out, how many of the contact person is from the state Kuala Lumpur. Let\u2019s say, you are going to analyse using the function COUNTIF(). This is the argument for COUNTIF() function.<\/p>\n\n\n\n
What will you be entering into the criteria field? \u201cKuala Lumpur\u201d ?<\/p>\n\n\n\n
Or \u201cWilayah Persekutuan\u201d ?<\/p>\n\n\n\n
With either of the entry, you answer is not going to be correct and you are going to end up with an inaccurate analysis.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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\u2019re going to collect is simply the phone number from the same country, then don\u2019t bother to record the phone number country code. But if the data that you\u2019re 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\u2019ll need to collect.<\/p>\n\n\n\n
Okay, let\u2019s say, the information that I\u2019m 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.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
If the amount of digit enter is less than 1,000,000 or more than 99,999,999, error will be prompted.<\/p>\n\n\n\n
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 \u2013 Dec, so List will be one of the best validating selection.<\/p>\n\n\n\n
In this case, we\u2019re going to validate the State field using List.<\/p>\n\n\n\n
Now you will be able select the state from a list of selection.<\/p>\n\n\n\n
Here are the related posts on this topic. Check out more:<\/p>\n\n\n\n
Microsoft Excel : Deadly sins you should not do in Excel (Part 1)<\/p>\n\n\n\n
Don\u2019t! Do not change the formatting of date (Part 2)<\/p>\n\n\n\n
Each data belongs to its own column (Part 3)<\/p>\n\n\n\n
No Blank cells, column, rows & Merging cells (Part 4)<\/p>\n\n\n\n
Do not rely on hard-coding values while constructing formula in Excel (Part 5)<\/p>\n\n\n\n
Make use of all kinds of reference in Excel, don\u2019t just stick with Absolute Reference $A$1 (Part 6)<\/p>\n\n\n\n
The type of report that is killing you slowly, inside out! (Part 7)<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":4,"featured_media":427,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1039","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/comments?post=1039"}],"version-history":[{"count":3,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1039\/revisions"}],"predecessor-version":[{"id":2212,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/posts\/1039\/revisions\/2212"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media\/427"}],"wp:attachment":[{"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/media?parent=1039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/categories?post=1039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.info-trek.com\/blog\/wp-json\/wp\/v2\/tags?post=1039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}