Validating Data

Problem

You want to make sure users of your spreadsheet don't enter inappropriate data.

Solution

Use Excel's Data Validation feature.

Discussion

Excel allows you to specify what constitutes valid data for any given cell. For example, say you had a spreadsheet that performed some calculations given a value that was input in a particular cell by a user other than yourself. Let's say you want to restrict the range of values the user can enter in the input cell, in order to minimize the possibility of misuse. Such a situation could arise if, say, you write a spreadsheet that allows you to interpolate data based on a regression equation. In such a case, you would want to restrict the independent value input by the user to within the allowable bounds of the regression analysis. Basically, you don't want the user to attempt to extrapolate beyond the data range used in the regression analysis, as such extrapolations can sometimes yield dangerously inaccurate results.

To specify validation for a cell, select the cell you want to set validation for and then open the Data Validation dialog box by selecting Data images/U2192.jpg border=0> Validation... from the main menu bar. Figure 1-21 shows the Data Validation dialog box.

Figure 1-21. Data Validation dialog box

The Allow drop-down listbox allows you to select the type of data to allow (for example, a decimal number, a whole number, a date, or text). Once you select the Allow type, the other controls will present additional qualifiers. In the example shown in Figure 1-21, I set 0 to 560 as the valid data range. You can also specify ranges greater than some value, less than some value, and so on. Once you press OK, these changes take effect. If the user attempts to enter data that does not fit the valid data criteria, a message box will appear indicating bad data entry.

You can specify exactly what you want the error message to display by entering your message under the Error Alert tab of the Data Validation dialog box. Further, clicking the Input Message tab allows you to specify an information message to be displayed to the user whenever a cell using data validation is selected. This is useful for giving your users a clue as to what constitutes valid data for any particular cell.