How can we help you?
Product:

When creating documents such as fill-in forms, it is important to make the document easy to work with, ensure a consistent format and eliminate errors when entering information. For this purpose, MyOffice Spreadsheet application can be configured to validate the input data for selected cells. In such cells, the user can select a valid value from a drop-down list or enter a value directly into the cell.

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:

In the Data menu, select Data Validation.

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

On the Sidebar, click data_check_sidepanel 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_cells Select Values, select the desired cells in the document, and click select_cells 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.

The range of the data source is to belong to a single column or row.

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 enterEnter. 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_check Data Validation.

On the Sidebar, click data_check_sidepanel 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:

Criterion

Start date

End date

Result

Between

Blank

Filled in

Error if the value is greater than the end date.

Filled in

Blank

Error if the value is less than the start date.

Not between

Blank

Filled in

Error if the value is less than or equal to the end date.

Filled in

Blank

Error if the value is greater than or equal to the start date.

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

Criterion

Start date

End date

Result

Between

Blank

Filled in

Error if the value is greater than the end date.
The cell being edited may be blank.

Filled in

Blank

Error if the value is less than the start date.
The cell being edited can be blank.

Blank

Blank

Error.
The cell being edited may be blank.

Not between

Blank

Filled in

Error if the value is less than the end date.
The cell being edited cannot be blank.

Filled in

Blank

Error if the value is greater than the start date.
The cell being edited cannot be blank.

Blank

Blank

No error.
The cell being edited cannot be blank.

Equal to

Blank

Error.
The cell being edited may be blank.

Not equal to

Blank

No error.
The cell being edited cannot be blank.

Greater than

Blank

No error.
The cell being edited cannot be blank.

Less that

Blank

Error.
The cell being edited may be blank.

Greater than or equal to

Blank

No error.
The cell being edited may be blank.

Less than or equal to

Blank

Error.
The cell being edited may be blank.

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

Criterion

Prerequisite for successful validation

Between

The date shall be within the interval between the start date and the end date inclusive.

Not between

The date shall be beyond the interval between the start and end dates.

Equal to

The date shall coincide with the value in the Date field.

Not equal to

The date shall not coincide with the value in the Date field.

Greater than

The date shall be later than the date specified in the Date field.

Less that

The date shall be earlier than the date specified in the Date field.

Greater than or equal to

The date shall be later than or the same as the date specified in the Date field.

Less than or equal to

The date shall be earlier than or the same as the date specified in the Date field.

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_cells 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 enterEnter. 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.

Drop-down lists inserted as a control or ActiveX are not supported.

Edit data validation

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_check Data Validation.

On the Sidebar, click data_check_sidepanel 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 data_check_sidepanel 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_validation Edit in the line with the validation.

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

Delete data validation

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_check Data Validation.

On the Sidebar, click data_check_sidepanel 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 data_check_sidepanel 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_validation Delete in the line with the validation and confirm the deletion in the dialog box that appears.

Actions with cells containing data validation

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.

Enter a date

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 Enter or fx_accept_icon 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 data_checkin_close button at the top of the message or by pressing Esc. Enter the date.

Enter a value in a cell with a drop-down list

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 Enter or click the fx_accept_icon 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 data_checkin_close button at the top of it or by pressing Esc. Enter the correct value manually.

If the Show value list setting is disabled, the error message is displayed without a drop-down list.

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

Action

Keyboard shortcut

Open the list

alt Option+

Select a value

and

Apply value

enter Enter

Select the first value of the list

Fn+

Select the last value of the list

Fn+

One screen down

Fn+

One screen up

Fn+

Close the list

Esc

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.

Was this helpful?
Yes
No
Previous
Number formats
Next
Remove duplicates