Skip to main content

Use data validation

Data validation is a tool that allows you to limit the input of data into cells to specified rules. You can create a drop-down list with valid values or customize validation for data such as dates.

You can copy a data validation condition to adjacent cells using autofill.

Data validation cannot be assigned to:

  • headers and summary rows of smart tables;
  • cells in pivot tables;
  • cells that already contain another data validation;
  • hidden cells.

Cells with data validation can be dragged by the corner to copy the condition to adjacent cells.

Open the Data Validation window and create a data validation​

  1. Select a cell or range of cells.
  2. On the sidebar, click the All validations button.
  3. On the All validations panel, click the Add validation button.
  4. Configure the data validation, tooltip, and error message (see below).
  5. Click OK.

You can also use the Data validation tool on the Data tab.

Configure data validation based on a specified list of values​

  1. On the Parameters tab, in the Type of data list, select List.
  2. Specify the Value source.
  3. If the cell must be filled, disable the Ignore blank cells option.
  4. If you do not want to use the drop-down list, disable the Show values lis option. You will have to enter the values manually, but the check against the list will still work.

You can set the Value source in the following ways:

  • Enter values separated by semicolons (for example: Excellent;Good;Satisfactory).
  • Enter the range address (e.g., A1:A5) or select cells on the sheet.
  • Enter the name of a named range.
  • Enter a formula that returns a range.

The list of values is always up to date. If the source uses a formula, the list will automatically update when its arguments change.

Configure data validation for dates​

  1. On the Parameters tab, in the Type of data list, select Date.
  2. Select the desired Verification criterion.
  3. Depending on the validation criterion, set the Start date, End date, or Date parameters.

You can enter values in the following format:

  • A specific date in any supported format.
  • A cell reference (for example, B1).
  • A formula (e.g., =TODAY()+6).

Configure a tooltip for data validation​

  1. On the Hint tab, enable the Show when cell is selected option.
  2. Fill in the Title and Message.

When you select a cell, a tooltip with the specified title and text will appear.

Configure an error message for data validation​

  1. On the Error message tab, enable the Show when invalid values are entered option.

  2. In the Message effect section, select the response to an incorrect entry:

  3. Fill in the Title and Message. If you leave them blank, the standard system message will be displayed.

If you enter incorrect data in a cell, an error message will appear.

Edit data validation​

  1. In the sidebar, click the All validations button.
  2. If necessary, select a filter for the validations, such as All validations or List.
  3. Hover the cursor over the desired validation. The corresponding cells will be highlighted on the sheet.
  4. Click the Edit button next to the validation name.
  5. Make the necessary changes.
  6. If necessary, enable or disable the Apply to all cells with the same validation settings option. If you disable the option, a new, unique validation will be created for the selected cells.
  7. Click OK.

You can also use the Clear all tool in the Data validation window to reset all data validation settings.

Delete data validation​

  1. On the All validations pane, hover over the desired validation. The corresponding cells will be highlighted on the sheet.
  2. Click the Delete button next to the validation name and confirm the action.