Advanced Spreadsheets – Validating

Set and edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time.

To set validation criteria for data entry in a cell range, click on ‘Data’ from the main menu bar, and from the resulting menu, click on ‘Validity’. This will open the ‘Validity’ dialog box.

In this dialog box, under the ‘Criteria’ tab, we have certain options to set validations for the input data. From ‘Allow’ drop-down list, we can choose the type of data that we want to allow in the particular cell or cell range. This can be whole number, decimal, date or time. We can also set some particular set of values for these data types. After making all the changes, click on ‘OK’. The validation will get applied to the cell range.

To set validations for a list, in the same dialog box, select ‘List’ from the ‘Allow’ drop-down. After this, you can set the different options from the available functions and you can even set the allowed entries for the list under ‘Entries’. After making the changes, click on ‘OK’. The validation will be set for the list.

Enter input message and error alert.

The input message is the message which gets displayed alongside the cell when that cell is selected. This message can be used to give the users of the spreadsheet some hint about the kind of validations which have been set for the cell and the kind of data which this cell can take.

To enter an input message, in the same ‘Validity’ dialog box, under the ‘Input Help’ tab, we have the ‘Contents’ field. In this tab, simply check the ‘Show input help when cell is selected’ checkbox and enter the title and text for the input message. After this, click on ‘OK’. The input message will be set for the cell.

An error alert is the message which is displayed when some invalid value is input to the cell. To set the error alert, in the same ‘Validity’ dialog box, under the ‘Error Alert’ dialog box, check the given checkbox and then enter the message details in the fields provided. After this, click on ‘OK’. This will set the error message for the cell.