Add data validation

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

1.Select the cell or range of cells.

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

•On the Data tab, click  Data validation.

•When working in macOS, select the Data > Data validation command from the command menu.

•On the Sidebar, click  All validations and in the All validations pane that appears, click Add validation.

3.On the Options tab, in the Type of data drop-down list, select List.

4.If you want the cell to be required to be filled in on entry, clear the Ignore blank cells checkbox. If the cell is not filled in, an error will be displayed.

5.If you do not want to use the drop-down list, clear the Show value list checkbox. In this case, the validation will be carried out after you enter a value directly into the cell, as described in the Enter a value into a cell with a drop-down list subsection.

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

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

•Specify the cell range manually.

•Click  Select values, select the desired cells in the document, and click  Finish selection.

•Enter the name of the named range or a formula that results in a cell range.

•If you make an error when entering, follow the on-screen prompts. 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.On the Input message tab, you can enter the title and text of the tooltip with information about the rules of working with the field. If you uncheck the Show when cell is selected box, the tooltip will not be displayed, but the title and text of the tooltip will be retained.

8.On the Error message tab, you can configure the behavior when invalid values are entered and the error text that explains to the user the cause of the error:

•The Message effect section is used to set the behavior when an invalid value is entered: Input prohibition option will not allow entering an invalid value into the cell, Warning option will display an error message but will allow entering a value.

•You can enter your own title and description of the error in the Title and Message fields. If you leave the fields blank, the default text will be displayed.

9.To complete adding the validation, click OK or press Enter. To cancel creating the condition, click Cancel or press Esc.

The values in the drop-down list are always up-to-date. For example, if there is a function result in the source and the function's arguments change, the corresponding value in the list is updated.

The drop-down list displays the values as they appear in the source. In a cell, the display of the selected value is determined by the format of that cell. For example, if the drop‑down list contains dates, and the General or Text format is selected for the cell, then the selected date in the cell will be converted according to the format: the date 02.02.2020 will be displayed as the value 43863.

To validate data in Date format, a separate validation type is available. To create an input date validation for a cell, follow these steps:

1.Select the cell or cell range for which the validation will be performed.

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

•On the Data tab, click  Data validation.

•When working in macOS, select the Data > Data validation command from the command menu.

•On the Sidebar, click  All validations and in the All validations pane that appears, click Add validation.

3.On the Options tab, in the Type of data drop-down list, select Date.

4.Check the Ignore blank cells box if you want the data validation cell to be left blank. With this option checked, the following input situations are possible depending on the selected Validation criteria if the date fields refer to empty cells:

With this box unchecked, the following input situations are possible if date fields refer to empty cells:

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

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

•Date in any supported data display format.

•Reference a cell or range, or select cells using the  Select values button. The date in a cell can be specified directly, using a function or a reference to another cell.

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

7.On the Input message tab, you can enter the title and text of the tooltip with information about the rules of working with the field. If you uncheck the Show when cell is selected box, the tooltip will not be displayed, but the title and text of the tooltip will be retained.

8.On the Error message tab, you can configure the behavior when invalid values are entered and the error text that explains to the user the cause of the error:

•The Message effect section is used to set the behavior when an invalid value is entered: Input prohibition option will not allow entering an invalid value into the cell, Warning option will display an error message but will allow entering a value.

•You can enter your own title and description of the error in the Title and Message fields. If you leave the fields blank, the default text will be displayed.

9.To complete adding the validation, click OK or press Enter. To cancel creating the condition, click Cancel or press Esc.

When creating validation conditions, the following restrictions apply:

•You cannot configure data validation for the header and total row of tables, pivot table cells, and cells that already contain other validations.

•Data validation can only be configured for visible cells.

MySpreadsheet also supports working with cells for which data validation is configured in third-party editors.