Formula Validation: A Better Way to Do Data Validation

Data validation is one of the most popular features in Excel. For those unaware of what Data Validation is, you’ve probably seen it before. It usually takes the from of those drop down boxes you see, limiting the inputs available to you for a particular cell. You can also limit inputs without a drop down box, such as ensuring date format entry or only allowing integers to by entered.

The problem with Data Validation? Unless you completely lock your sheet, someone can copy and paste over your validated cell. It doesn’t matter how much time you may have spent setting up the validation in the first place and making it clear to the user what options they can put in. If they copy and paste over it, it’s all for not.

Formula Validation

While formula validation does not prevent a user from doing this, it makes it much easier for you to track these occurrences. Creating formula validations is a relatively simple process.

Recreate the Data Validation with an IF statement

Simple enough. If your validation includes a list, simply use an ISNA statement and VLOOKUP as your validation. If the VLOOKUP turns up a #N/A, you’ll know that the input was not from your predefined list.

Error Counting and Error Finding

The beauty of this formula is that it provides you a count of errors. As long as you set the false condition of the IF Statement to result in the number “1”, you can easily get a count of all errors that have occurred in your spreadsheet by summing up the formula validations. This is incredibly helpful when you realize someone creates a data validation error and you need to know where it is. Simply go to your validation columns and look for the “1’s”. Below is an example of Formula Validation sums from multiple spreadsheets. As you can see, I know exactly how many errors I have and where they occurred.

Next Steps

So is Formula Validation really better than Data Validation? In some cases it is, in other cases, it may not be worth the trouble. My recommendation is, when dealing with a spreadsheet with a large number of inputs and multiple untrained users, use both data validation and formula validation to fully ensure the integrity of your spreadsheet.