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:

•In the Data menu, select Data Validation.

•On the Toolbar, in the Data section, click  Data Validation.

•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, uncheck the Ignore blank cells box. If the cell is not filled in, an error will be displayed.

5.If you do not want to use the drop-down list, uncheck the Show value list box. 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 button 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:

•In the Data menu, select Data Validation.

•On the Toolbar, in the Data section, click  Data Validation.

•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 button or press Esc.

When creating validation conditions, the following restrictions apply:

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

•Data validation can only be configured for visible cells.

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

You can edit data validation using the Data Validation dialog box or All Validations pane.

To edit data validation using the Data Validation dialog box, follow these steps:

1.Select the cells that contain the validations.

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

•In the Data menu, select Data Validation.

•On the Toolbar, in the Data section, click  Data Validation.

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

3.Change the validation parameters in the dialog box. If the Apply to all cells with the same validation setting box is checked, the changes will be applied to all validations with the same condition. If the box is unchecked, a validation with new conditions will be created after editing.

4.Click OK.

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

1.On the Sidebar, click  All Validations.

2.In the opened list in the pane, find the validation. The cell with the selected validation will be highlighted on the sheet.

3.Click  Edit in the line with the validation.

4.Change the validation settings in the dialog box and click OK.

You can delete data validation in one of the following ways:

•Using the Data Validation dialog box.

•Using the All validations pane.

•Delete columns or rows with cells with validation.

•Delete the sheet containing the cells with validation.

•Apply autofill for cells without validation to cells with validation.

To delete data validation using the Data Validation dialog box, follow these steps:

1.Select the cell or range of cells containing the validation.

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

•In the Data menu, select Data Validation.

•On the Toolbar, in the Data section, click  Data Validation.

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

3.Click Clear All in the bottom of the dialog box. If you want to delete such validation from all cells, check the Apply to all cells with the same validation settings box.

4.Click Save.

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

1.On the Sidebar, click  All Validations.

2.In the opened list in the pane, find the validation. The cell with the selected validation will be highlighted on the sheet.

3.Click  Delete in the line with the validation and confirm the deletion in the dialog box that appears.

You can drag one or more cells with data validation to autofill cells on the left, right, bottom, or top.

You can cut, copy and paste cells with data validation.

When you merge cells, the data validation condition is saved only from the upper-left cell of the range.

When you split cells:

•The data validation condition is retained in the upper-left cell of the range if it was defined for that cell before the cells were merged.

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

If a column or row that contains cells with data validation is selected on the sheet, the cells with data validation are copied to the new columns/rows when new columns or rows are pasted.

If a cell is configured to validate the specified date, in such a cell, you can enter:

•Number. The application will automatically convert it to the appropriate date. For example, the number 1 will be converted to the date 12/31/1899.

•The date in any supported data display format, including time indication. For example, 07/12/2023 10:30 AM.

•Cell reference (for example, =B1). The date in a cell can be specified directly, using a function or a reference to another cell.

•Function (for example, =TODAY()+6). The function can be of any kind.

An error message is displayed if:

•You enter a date, a cell reference, or a function that does not comply with the validation.

•You enter text into the cell or Text format is selected for the cell.

To enter a date in a cell, follow the steps below:

1.Select the cell. A prompt for entering the date will be displayed above the cell, if it was created.

2.Enter the date in one of the ways described above.

3.Press  Enter or button in the Formula bar. If the date is not entered as a reference or function, you can also click any other cell in the sheet or any column/row header.

If the date satisfies the validation conditions, you will quit the cell editing mode. The Date format is applied to a cell if another format was selected for the cell before entering the value (with an exception of the Text format).

If the date does not meet the validation conditions, then:

•The date is highlighted.

•The cell remains in editing mode.

•An error message is displayed.

To close the error message and enter the correct date, do one of the following:

•Start entering the date. The error message will close automatically.

•Close the error message by clicking the  button at the top of the message or by pressing Esc. Enter the date.

To enter a value in a cell with a drop-down list, follow these steps:

1.Select the cell. A prompt for entering a value will be displayed above the cell, if it was created.

2.Do one of the following:

•Expand the drop-down list and select the desired value. To work with the drop-down list, you can use the mouse or keyboard keys (see the table).

•Enter the value manually. The entered value must match character by character with the required value in the drop-down list. The case of characters is not taken into account when entering a value. Press  Enter or click the  button in the Formula. bar, or click any other cell in the sheet/header of any column/row.

If the value entered manually is incorrect, the drop-down list is automatically expanded. An error message is displayed at the top of the list.

To edit the entered value, do one of the following:

•Select the correct value from the drop-down list.

•Start entering the correct value manually. The drop-down list will automatically close.

•Close the drop-down list by clicking the  button at the top of it or by pressing Esc. Enter the correct value manually.

The table lists the keyboard shortcuts that are used when working with the drop-down list.

The commands One screen down and One screen up are used to quickly view long drop-down lists. When executing commands, the list scrolls to the next or previous items.