Excel & data
Data Validation: The Cheapest Way to Stop Bad Data at the Door
Most spreadsheet errors start as a typing mistake. Excel's data validation can prevent many of them before they enter the sheet.
Prevent, do not just detect
Checking a sheet for errors after the fact is slow. It is cheaper to stop a wrong entry when it is typed. Excel's data validation lets you set a rule for a cell or a range so that only acceptable values are allowed.
The rules you can set
According to Microsoft's documentation, you can allow whole numbers, decimals, items from a list, dates, times, text of a certain length, or values that satisfy a custom formula. You can also show an input message when a cell is selected and an error message when an entry breaks the rule. A drop-down list for status, platform or product category removes a whole class of spelling variations that break totals later.
Know the limits
Validation can be applied to cells that already contain data, but Excel does not automatically flag existing entries that break the new rule. Microsoft's help describes the Circle Invalid Data tool for finding them. Validation settings also cannot be changed while a workbook is shared or a sheet is protected, so plan the rules before you share the file. And validation only checks what is typed, not whether the formulas behind it are right.
Make it a habit
When we build input sheets for our Excel tools, every entry cell has a rule and a short prompt, and every list comes from a single source table. It takes a little time at the start and saves much more later.
