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:

inconsistent data

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 2016-2021 and Office 365, click on the Data Tab and choose Data Validation:

data validation on excel menu

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:

data validation window

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, 2020. Just select the data entry cells (column A below), Data-Validation, and specify the criteria:

    data validation criteria

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

     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:

    set validation criteria
  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:

    autofill example

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

    hide rows

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

    autofill

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:

    create data validation 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).

    criteria source range

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

 example of validation 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:

error alert example

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

 error alert 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.


Stop using old technology!

Upgrade Your Excel and Data Analysis Skills to Smart Charts Using QI Macros.

Track Data Over Time

Primitive Chart
line graph
Line Graph
Smart Chart
control chart
Control Chart

Compare Categories

Primitive Chart
pie chart
Pie Chart
Smart Chart
pareto chart
Pareto Chart

Analyze Variation

Primitive Chart
bar or column chart
Bar or Column Chart
Smart Chart
histogram
Histogram

QI Macros add-in for Excel makes creating smart charts a snap.