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:
Bad Data Validation in Excel 

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 2000-2003, click on Data-Validation..
Excel 2000-2003 Data Validation 

In Excel 2007-2010, click on the Data Tab and choose Data Validation:
Excel 2007 - 2010 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: 

Excel Data Validation for Dates

In the case of the travel group, it was time to mistake-proof the data entry form.

  1. To clean up the dates, Excel's Data Validation can require specific formats. In this case, a date after Jan. 1, 2010. Just select the data entry cells (column A below), Data-Validation, and specify the criteria. 

    Data Validation setting for dates

    If a travel agent tries to put in an incorrect date, Excel will tell them it's invalid:

    Excel Data Validation Error Message 

    Using the other two Data Validation tabs, you can customize this message and error. More about that in a minute. 
  2. 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. 

    Excel Data Validation Custom Formula
  3. 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:

    Excel Data Autocomplete

    Then, use Format-Row-Hide to hide rows 2-11:

    Data Validation hide rows

    With rows 2-11 hidden, agents will be prompted with destinations and routes when they begin to type:

    Excel Data Autocomplete

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.

  1. 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:

    Excel Data Validation Time Sheet List
  2. 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). 

    Data Validation against list

    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:

    Data Validation in Excel using a list 
  3. Then just hide column "I" and save the workbook.

Custom Prompts

There are two other tabs on the Data Validation Menu: Input Message and Error Alert. 

Input Messages 

If you want to prompt people every time they enter data into a cell, Input Message can help them format it correctly: 

Excel Data Validation Input Message 

This can be a little intrusive after people learn how to use the data sheet. 

Error Messages 

Or specify an error message that only appears when users enter an invalid value: 

Create Excel Data Validation error message

If a user tries to enter four hours and thirty minutes as 4:30, they will get this error message: 

Excel Data Validation error message 

Here's My Point

Gathering 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.

Create these charts and diagrams yourself in just seconds using the QI Macros for Excel...
Try It Now!


How We Can Help Download Free QI Macros Trial Buy QI Macros for Excel Now