Data Validation In Excel
Sometimes the wrong data gets typed into a cell in Excel. For instance,
someone types a false date like April 31st or puts text where a number
belongs. These types of errors can cause havoc in a spreadsheet. For a
spreadsheet that has a long chain of calculations, an error in one place can
propagate all over.
If an ounce of prevention is worth a pound of cure, then an ounce of data
validation is worth a pound of spreadsheet auditing. Data validation can
prevent mistakes from happening in the first place.
The dialog box shown above gives a good idea of what validation is for.
You select an area of the spreadsheet, typically an entire column, and then
go to Data > Validation. In the dialog box you can set the column only to
accept dates, members of a list like Huey, Duey or Luey, or numbers between
0.0 and 1.0.
The custom data validation screen provides the ability to validate values
based on formulas and other values on the spreadsheet. It’s very powerful.
The Journal of Accountancy published an article we wrote on data
validation called “Block That Spreadsheet Error.” In the article, we
describe the features of data validation much more fully. Take a look at the
complete article on line at http://www.aicpa.org/pubs/jofa/aug2002/callahan.htm
|
|