Excel's Data Validation Features
Mistake-Proof Data Collection
QI Macros customers use Excel to create forms for all kinds of data collection: time sheets, scorecards, even mini-databases. Unfortunately, when they try to analyze it with PivotTables, they soon discover that humans are very creative spellers. One hospital system spelled "Medicare" in various acronyms: MDCR, Medcr, Medicr, etc. This makes it difficult to do any data analysis or mining without a lot of cleanup. Excel's Data Validation function can eliminate the confusion.
Data Validation with Excel
The travel department of a major company asked its travel agents to track flights using dates, route codes (e.g., DEN-LAX-DEN) and destination city. Travel agents found creative ways to make the analysis difficult: incorrect dates, swapping routes with destinations, misspelling destinations, leaving the hyphen out of the route. And so on:
How can the travel group ensure that travel agents enter the data correctly? Data Validation. Simply select the cells to be used for data entry then select Data Validation from Excel's menu.
In Excel 2013-2021 and Office 365, click on the Data Tab and choose Data Validation:
This will pop-up a separate data validation window. Choose the Settings tab and then set the data validation criteria: integers, decimals, dates, times, text, list or custom:
In the case of the travel group, it was time to mistake-proof the data entry form.
- To clean up the dates, Excel's Data Validation can require specific formats. In this case, a date after Jan. 1, 2020. Just select the data entry cells (column A below), Data-Validation, and specify the criteria:
If a travel agent tries to put in an incorrect date, Excel will tell them it's invalid:
Using the other two Data Validation tabs, you can customize this message and error. More about that in a minute.
- Select Column B (the route) and choose "Custom" and insert a formula to check for a hyphen in character four. Excel's MID function can check the cell for a hyphen. In this case, MID looks in the cell, at character 4, for a length of one: MID(B1,4,1). Since I selected the entire column, I used the first cell, B1, as the starting point and it will apply to all of the cells:
- The destination column is a bit more challenging. Data Validation will let you specify a list of values, but travel destinations might be too varied. Excel, however will autofill a cell after a few characters, so we could enter the ten most common destinations in C2:C11. Then, when an agent starts to type in that column, the destination will appear (e.g., San Francisco). If trips all originate from a common destination (e.g., Denver), we could enter routes as well:
Then, use Format-Row-Hide to hide rows 2-9:
With rows 2-9 hidden, agents will be prompted with destinations and routes when they begin to type:
Time Sheet Example
A customer was trying to get valid time sheets in Excel. The hours were rounded to the nearest half hour. But employees kept putting in values like 4:30 instead of 4.5. Here's how easy it can be to solve that problem with data validation.
- First, in an empty column, enter 0 in the first cell(I2). then a formula (=I3+0.5) in the next cell and copy/paste the formula down to get 24 hours:
- Then select columns B:G, then Data Validation, then select List in the criteria pull down menu. Next specify the range for the source list (=$I$2:$I$50).
This will add a dropdown list to every cell so that employees can type a valid time (e.g., 2.5) or select a valid time:
- Then just hide column "I" and save the workbook.
There are two other tabs on the Data Validation Menu: Input Message and Error Alert.
If you want to prompt people every time they enter data into a cell, Input Message can help them format it correctly:
This can be a little intrusive after people learn how to use the data sheet.
Or specify an error message that only appears when users enter an invalid value:
If a user tries to enter four hours and thirty minutes as 4:30, they will get this error message:
Here's My PointGathering consistent, error-free data is one of the keys to process improvement. To create powerful Excel-based tools and improvement stories without a lot of data cleanup, you will need data entered in a consistent way.
Excel's Data Validation functions will train users to enter data correctly. Users can spend days in training or Excel can just force them to learn the right way to enter data. Using Excel is faster and more effective.
Mistake-proof your data collection. It's just this easy.
Isn't it Time to Upgrade Your Excel Charting Skills?
Stop trying to make your spreadsheets easy to read. Nobody wants to read your spreadsheet!
And stop using primitive line, pie and bar charts.
Isn't it time to graduate to Smart Charts: Control Charts, Pareto charts and Histograms?
Stop Struggling with Excel Charts!
Start creating Smart Charts in just minutes.
Download a free 30-day trial. Get Smart Charts for Excel now!
Smart Charts Included in QI Macros for Excel