Data Validation: Part 1 of 3
September 30, 2011 2 Comments
If you work with data often, and especially if you collaborate or share your information and work with others, you know this to be fact: like my kids when they tell me they are going to sleep when I know they’re not; if left unsupervised, your colleagues will undoubtedly screw up your spreadsheet. Of course that is partly poking fun, but you get the point and can certainly relate. One of the biggest problems I’ve confronted over the years working with data has to be maintaining accurate and easy data entry.
Fortunately, the two–accuracy and simplicity– go hand in hand. Making the process simple by reducing the potential for human error is paramount. This cannot be understated and serves two purposes. As mentioned in one of my favorite books, The E-Myth Revisited by Michael E. Gerber, one of the biggest mistakes people in business make is making the success of their company, department, or group too heavily dependent on smart people and excellent workers. While I certainly understand the importance of a good team, something that revealed itself time and time again while playing sports my entire life, I also know that having a great structure and process that allows players or employees of different types and skills to succeed, is as important and effective. Making the structure of the “program” simple and effective is step one. Once that is accomplished, communicating that structure clearly and explicitly so that those charged with carrying out the plan will be successful is step two and a natural extension of step one.
In the working world, this theory manifests itself in many ways. One small way is in the countless reports, processes, and other forms of communications we all share between and amongst each other. Within that context, Data Validation in Microsoft Excel can be an extremely helpful tool. Instead of allowing free and manual entry, I am of the mindset that whenever possible you should restrict the possibilities people have and, therefore, probability of error. Not only does this ensure accurate information is collected and shared. But it also allows employees to perform their jobs well, thus enjoying them more, and frees them up to perform more important duties and grow professionally in business-critical areas.
It’s really pretty simple. To make sure data is entered correctly, apply data validation to a cell, by doing the following:
- Select the cell on which you’d like to apply validation
- On the “Data Tools” Ribbon, select Data Validation/Data Validation; the “Data Validation” window will appear
There are three tabs on the “Data Validation” pop-up
Settings: Under ‘Validation Criteria’, select “List” for the “Allow:” option
- The “Source:” box will then appear. You can either type in the options, separated by a comma; or enter a range of cells that contains the list of values you allow.
- Input Message: Allows you to enter text that appears when the cell with validation is selected
- Error Message: Allows you to customize the message that appears if invalid data is entered
Very simple, right? Well, what if you want to use a range for the list, but don’t want it to appear on the same sheet, cluttering up things. In part two of this series, I’ll show you a trick for that.