Data validation

–Data validation cannot be assigned to:

•headers and summary rows of smart tables;

•cells in pivot tables;

•cells that already contain another data validation.

–Data validation is only set on visible cells.

–Merging cells:

•When merging, the validation condition is retained only from the top left cell.

•When splitting cells, the validation condition set for the merged cell will be applied to all cells in the resulting range. If a value from the drop-down list was selected in the merged cell, it will remain only in the upper left cell.

Open the data validation window

1.Select a cell or range of cells.

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

•On the Data tab, from the Data validation menu, select Data validation.

•On the sidebar, click All validations and in the panel that opens, click Add validation.

3.Select the type of validation.

List type validation (drop-down list)

Used to create a drop-down list of predefined values.

1.On the Parameters tab, in the Type of data list, select List.

2.Customize additional options:

•Ignore blank cells: uncheck this box if the cell must necessarily be filled.

•Show values list: uncheck this box if you don't want to use a drop-down list (values will have to be entered manually, but the list check will work).

3.Specify the source of the values in the Value source field in one of the following ways:

•Manual: enter values with a semicolon (for example: Excellent;Good;Satisfactory).

•Cell range: enter the address of the range (e.g., A1:A5) or click the Select values button to select cells on the worksheet.

•Named range or formula: enter the name of the range or a formula that returns the range.

The values in the list are always current. If the source uses a formula, the list will automatically update when its arguments are changed.

Date type check

Allows you to limit the input of dates to a specific range or condition.

1.On the Parameters tab, select Date from the Type of data drop-down list.

2.Customize the validation criterion:

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

b.In the Start date and End date fields (or a single Date field, depending on the criterion), specify a value.

Available values:

–A specific date in any supported format.

–A cell reference (for example, B1).

–A formula.

Customizing the tooltip and error message

These settings are available for any type of test on the Hint and Error message tabs.

–Hint tab:

•Fill in the Title and Message fields of the tooltip that will appear when the cell is selected.

•Uncheck the Show when cell is selected checkbox if you want to disable the tooltip display (the data will be saved).

–Error message tab:

•In the Message effect section, select the response to the erroneous entry: Inhibit prohibition (blocks entry of an invalid value); Warning / Information (will show the message but allow you to continue entering).

•Fill in the Title and Message fields. If left blank, a standard system message will be displayed.

Completing the Data validation

–To apply the validation, press OK or the Enter key.

–To cancel the creation, press Cancel or the Esc key.

–To reset all fields in the window to their default values, click Clear all.

To change the parameters of an existing validation:

1.Select the cells with the validation and open the Data validation window (see above).

2.Make the necessary changes.

3.Check or uncheck the Apply to all cells with the same validation settings check box:

•If checked, the changes will affect all cells with identical initial settings.

•If unchecked, a new, unique validation will be created for the selected cells.

4.Click OK.

Alternative method

1.On the sidebar, click All validations.

2.In the list, locate the desired check — the corresponding cell in the workspace will be highlighted.

3.Click Edit in the row with the check.

4.Make your changes and click OK.

Delete a data validation via the Data validation window

1.Select the cell or range with the check.

2.Open the Data validation window.

3.Click the Clear all button.

4.To delete in bulk, select the Apply to all cells with the same validation settings check box.

5.Click OK.

To delete a data validation through the sidebar

1.On the sidebar, click All validations.

2.Click Delete in the validation row and confirm the action.

–Autocomplete: cells with a data validation can be dragged by the corner to copy the condition into neighboring cells.

–Value display: the value in the drop-down list is displayed as it appears in the source. The format in which this value is displayed in the cell itself is determined by the cell format.