Validate the data

To create a drop-down list with valid values, follow these steps:

1.Select a cell or a range of cells.

2.Open the Data validation window in one of the following ways:

•In the Command menu, select Data > Data validation.

•On the Sidebar, click All validations. In the All validations pane that opens, click Add validation.

3.On the Parameters tab, select the Type of data drop-down list and choose List.

4.Clear the Ignore blank cells option if you want the cell to be filled. If the cell is not filled, an error will be displayed.

5.Clear the Show value list option if you do not want to use the drop-down list.

6.In the Value source field, specify the values to generate the list in one of the following ways:

•Enter the values in the field directly, separating them with a semicolon.

•Specify the range of cells manually.

•Click Select source, select the required cells in the document and click Complete the selection.

•Enter the name of the named range or a formula that has a range of cells as its result.

•In case of an input error, follow the on-screen instructions. To clear all fields in the window and return the window to its default view, click Clear all at the bottom of the window.

7.In the Hint tab, you can enter the title and text of the prompt message with information about how to work with the field. If you disable the Show when cell is selected setting, the prompt message will not be displayed, but the title and text of the prompt message will be saved.

8.In the Error message tab, it is possible to set what happens when invalid values are introduced and the error text explaining the reason for the error to the user:

•In the Message effect section, set what happens when entering an invalid value: Input prohibition to prevent from entering an invalid value into the cell, Warning to display an error message, but allow to enter the invalid value.

•In the Title and Message fields you can enter your the title and description of the error according to your preferences. If these fields are left empty, the default text will be displayed.

9.To complete the process of creation of the validation, click OK or press Enter (  Return) on the keyboard. To cancel the creation process, click Cancel or the Esc key on the keyboard.

The values of the drop-down list are always relevant. For example, if there is a formula result in the source, and the formula's arguments change, then the corresponding value is updated in the list.

The drop-down list displays the values in the same format as they appear in the source. The format of a cell determines how the selected value is displayed.

There is a separate type of validation for data in the Date format. To add data validation for a cell, follow these steps:

1.Select the cell or range of cells for which the validation will be added.

2.Open the Data validation window in one of the following ways:

•In the Command menu, select Data > Data validation.

•On the Sidebar, click All validations. In the All validations pane that opens, click Add validation.

3.On the Parameters tab, select the Type of data drop-down list and choose Date.

4.Select Ignore blank cells if you want the cell with the data validation to be left empty.

5.In the Validation criteria drop-down list, select the validation condition.

6.In the Start date and End date or Date field, enter the following:

•Date in any supported data display format.

•Reference to a cell or range, or select cells using the Select source button. The date in a cell can be set directly, via a formula or a link to another cell.

•Formula (for example, =TODAY()+6).

7.In the Hint tab, you can enter the title and text of the prompt message with information about how to work with the field. If you disable the Show when cell is selected setting, the prompt message will not be displayed, but the title and text of the prompt message will be saved.

8.In the Error message tab, it is possible to set what happens when invalid values are introduced and the error text explaining the reason for the error to the user:

•In the Message effect section, set what happens when entering an invalid value: Input prohibition to prevent from entering an invalid value into the cell, Warning to display an error message, but allow to enter the invalid value.

•In the Title and Message fields you can enter your the title and description of the error according to your preferences. If these fields are left empty, the default text will be displayed.

9.To complete the process of creation of the validation, click OK or press Enter (  Return) on the keyboard. To cancel the creation process, click Cancel or the Esc key on the keyboard.

The following restrictions apply when creating validations:

•Data validation cannot be assigned to the header and totals row of smart tables, pivot table cells, and cells that already contain other validations.

•Data validation can only be set for visible cells.

One or more data validation cells can be stretched to autofill cells located on the left, right, bottom or top.

When merging cells, the data validation condition is saved only from the upper left cell of the range. When splitting cells:

•The data validation condition is stored in the upper-left cell of the range if it was set for this cell before merging the cells.

•The data validation condition is stored in each cell of the range if it was set for the merged cell. If the merged cell contained a drop-down list and a value was selected in it, then it will be saved only in the upper-left cell of the range.

To edit the data validation using the Data validation window, follow these steps:

1.Select the cells containing the validations.

2.Open the Data validation window in one of the following ways:

•In the Command menu, select Data > Data validation.

•On the Sidebar, click All validations. In the All validations pane that opens, click Add validation.

3.Change the validation settings in the window. When the Apply to all cells with the same validation settings setting is enabled, the changes will be applied to all validations with the same condition. If the setting is disabled, a validation with new conditions will be created after editing.

4.Click OK.

To edit a validation using the All validations pane, follow these steps:

1.On the Sidebar, click All Validations.

2.In the list that opens, find the validation. The cell with the selected validation will be highlighted on the sheet.

3.ClickEdit in the validation line.

4.Change the validation settings in the window and click OK.

To remove the data validation using the Data Validation data, follow these steps:

1.Select the cell or range containing the validation.

2.Open the Data Validation window in one of the following ways:

•In the Command menu, select Data > Data Validation.

•On the Sidebar, click All Validations. In the All Validations pane that opens, click Add Validation.

3.In the window, click Clear All. To remove such a validation from all cells, select Apply to all cells with the same validation settings.

4.Click Save.

To delete a data validation using the All Validations pane, follow these steps:

1.On the Sidebar, click All Validations.

2.In the list that opens, find the validation. The cell with the selected validation will be highlighted on the sheet.

3.Click Delete in the validation line and confirm the deletion in the window that appears.