Help Documentation

Data Validation

Apply Data Validation to Cells

You can use data validation to restrict the data that is entered in cells to a specific condition that you set. For example, if you want to restrict a numeric cell, you could add numeric constraints like the number should be less than 20, or between 40 and 60.

Similarly, you can use data validation to set rules on cells with text and date formats. You could also restrict a cell to a predefined list of values and bring up a drop-down list of allowed values when the cell is edited.

This section will guide you to restrict a cell to a predefined list of values.

To restrict a cell:

  1. In the Data tab, click Data Validation. The Data Validation - Create window is displayed.
  2. To enter a list of pre-defined values, select List in the Criteria drop-down list. Enter the values separated by a line in the box.
    To select the cell range in the sheet that contains the list of allowed values, in the Criteria drop-down list, select Cell Range. Point and select the range of cells you want to choose from the sheet.
  3. After you have selected either of the above options, a drop down list is displayed when you are about to edit a cell with the predefined values set by you. Auto-completion is also supported on these cells which helps in quick data entry and maintains data consistency.

Add Error Alerts and Help Text

This section describes how you can add an alert message to choose how the spreadsheet should respond when you give an invalid input. You can also add help messages to help the person editing the spreadsheet know what kind of input is accepted.

To add error message:

  1. In the Data Validation - Create window, click the Alerts and Help Text link.
  2. Select the Show Alert on invalid data entry checkbox, and choose the type of error alert from the drop-down list.
  3. You can choose from two types of error alerts - Stop and Information.

Stop prevents users from entering invalid data in a cell. While Information is used to inform users that the data entered is invalid and does not prevent users from entering it.

Note: You can customize the text that users see in an error alert message. If you choose not to do so, users see a default message.

To add help message:

  1. In the Alert and Help Text section, select the Help Text checkbox.
  2. Enter the required help message in the box.

The help text is generally used to guide the users to enter correct type of data. It will be shown when a user clicks on a particular cell.

Manage Data Validation Settings

The manage validation functionality lists all the Data Validation rules on your spreadsheet in a single view.

To manage data validation settings:

  1. In the Data Validation drop-down list, click Manage Validation. The Data Validation - Manage window is displayed.
  2. To edit any settings, click the settings icon beside each row. You can edit your existing rules, clear them or copy them to a new range. You can also filter the validation settings by sheet or cell range.