Integrity through Validation

Maintaining the integrity of your data is essential in the creation of meaningful reports. The old GIGO adage, “Garbage In, Garbage Out” is as relevant today as it was when an IBM instructor named George Fuechsel first used it 50 years ago.

Using the List option in the Validation tool in Excel is an excellent way of maintaining consistency and integrity within a spreadsheet. Combining it with a Named Range can make it more Effective and Efficient!

Let’s say you are using a dropdown box in a cell that refers to a List of customer service representatives in a large department. For convenience and to assure consistent spelling, you have created the dropdown box using the List feature in Validation.

If the department is large enough, updates to the Validation List will probably be a frequent and tedious task. Rather than having to put up with the hassle of adding/deleting employees from the reference list and then having to adjust the list range, you can simply Name a Range “CSReps”, (or whatever strikes your fancy), and refer to “CSReps” in the List dialogue box in Validation.

Just be sure to allow sufficient room in your CSReps range to add names in the future, and give it an occasional Sort to maintain an Alphabetical Ease-Of-Use.

This is one of those simple techniques that will further establish you as the Excel Guru you truly are.

Happy Excelling All!