Use Data Validation for Instructions

Your spreadsheet should be easy to use, easy to understand. One way you can make this possible is by including instructions for the users so they know exactly what they are supposed to do when entering data into the spreadsheet.

Data validation is a common technique used to ensure that the user does not enter the wrong data. Typically, though, the user only knows he has made an error after he enters the wrong data.

For example, you may have set the spreadsheet so a cell can only contain numbers between 1 and 100. If the user enters such a number, no problem. But if the user enters 0 or 101, then an error message pops up stating there is an invalid entry.

If you have done a proper job creating your spreadsheet, the message the user sees does not just say there is an error. Instead, the message says what the user did wrong and explains what the user is supposed to do to avoid an error.

But why use data validation just to tell the user he is wrong? Why not be more proactive and use data validation so a message box pops up when the user clicks on a cell? The message can tell the user what to input, or how a formula is calculated in that cell.

With this understanding, the user is more likely to enter the correct data the first time. It will be less annoying to the user to use the spreadsheet because he is getting information before he enters data, not being scolded by the spreadsheet because he made a mistake without knowing what he was supposed to do.

The picture below illustrates how to use data validation to show the instructions when the user clicks on a cell.

1 view