Spreadsheets offers features beyond Workbooks, such as real-time collaboration and authorship tracking. See Introduction to Spreadsheets for full details.

Data Validation

Data Validation guarantees that only data of a certain format or data within a certain range can be entered into any given cell or group of cells. This ensures data accuracy and consistency.

Data Validation Rules

To use Data Validation, you'll need to add data validation rules.

Make sure you have the cells selected for which you'd like to define restrictions. The cells that you select can be a block of cells or non-contiguous cells. Data Validation is found in the Data toolbar. Click the Validation button and then select Add Data Validation.

image alt text

Note that Data Validation is allowed on source cells.

Next, go to Allow and select the parameter you want to work with from the drop-down menu. If you want the user to enter a value from a specific set, you can use the Option List. You can also define your data validation based on Date, Decimal, Whole Number, or Text.

image alt text

Option Lists in Data Validations


If you select Option List from the Allow menu, you can enter values that will appear in a drop-down list for a user. You can define them however you like, with one value on each line. In this example, the values Yes, No, Maybe, and Not Applicable are used:

image alt text

Once your values are defined, click the Apply button. The cell(s) will now have a gray arrow that, when clicked, displays the options you've defined for that cell.

If you want to reuse lists, you can save and share them with others. Type a name for the list you've created in the List Name box. The list will automatically be saved.

image alt text

To reuse a list, add Data Validation to new cells, select Option List, click the drop-down arrow on the List Name box, and select it.

Once a list is saved, it can be used by other users who have edit permissions, and will appear when they apply Data Validation. Shared lists can only be used within the document they were created in.

Option lists can be renamed, edited, and deleted within the List Name drop-down.

image alt text

Only document owners can delete lists. If you edit a list, other users won't see your changes until you click Share.

NOTE: If a saved list is later edited, other cells using that list will retain their current value, but the choices on the list will change (possibly resulting in an invalid value). If a saved list is deleted, other cells using that list will still retain their last known list and value, but they will now be independent (i.e. they can no longer be bulk edited).

Date Options in Data Validations

Selecting Date from the Allow menu displays Data options, where you can define what dates a user can enter in cells.

There are a number of options available for dates, to help define either exact dates, a range, or a time before/after a specific date. Depending on the option you select, you will have one or two date boxes, where you can click the calendar button and select a date to define your parameters.

image alt text

Click the Apply button when your date(s) are defined. If a user attempts to enter a date that is outside of the defined parameters, they will get an error message.

Whole Numbers and Decimals in Data Validations

Similar to Dates, the Decimal option in the Allow menu displays Data options, where you can define the numeric decimal values for your cell(s). Values can be defined as any number, a range between (or not between) two numbers, equal/not equal to them, greater/less than, etc.

image alt text

When defining a range of numbers, you can use a range of cells to define each value. Click the spreadsheet button in a field and then select the cells in your table to populate the field.

image alt text

Note that the Data options display how values are entered, as displayed in Document Properties (for example, Entered in millions).

The Whole Numbers option in the Allow menu has the same options for cell definition, but for whole numbers instead of decimals.

For number scaling (displaying numbers in thousands, millions, etc.), an option is provided to validate whole numbers against the Actual Value or the Display Value.

After setting the Allow field in your validation rule to Whole Number, an additional field will appear for Validate Whole Number Against. You can select the Actual Value, which will validate against the exact number, or select the Display Value, which will validate against the number as shown in the cell.

For example, a cell that is entered in millions will accept 4.5 when validating against the Actual Value (exact number is 4,500,000), but reject 4.5 when validating against the Display Value.

Click Apply for this to take effect.

image alt text

Text Options in Data Validations

The Text option of the Allow menu displays Data options that define text parameters for your cell(s). You can define whether text contains certain words/characters, does NOT contain them, the minimum/maximum character lengths, or a range of characters.

image alt text

Editing and Removing Data Validations

After adding validation, if you go back to the Data Validation button, the drop-down now shows Edit Data Validation and Remove Data Validation.

Remove Data Validation clears all validation parameters from the selected cell(s).

Choosing Edit Data Validation allows you to change your cell definitions, or to add a second validation rule (for example, allowing text within the selected cells, adding a number between 1 and 100, etc.).

Copying and Pasting Data Validations

When a cell with a rule is copied and pasted, the clipboard will contain a checkbox Apply Data Validation.

image alt text

If checked, the rule will be applied to the pasted cell, possibly overwriting any previous rules.

If the Set Default box is also checked, then future copy/pastes will also apply rules if the copied cell contained a rule. When a cell without a rule is copied and pasted onto a cell with a rule, the pasted cell will retain its rule. Format Painter does NOT apply data validation rules.

Viewing Data Validations

After creating your validation rules, you may want to view them. Wdesk can display all validation in a clear, visual manner so that you can view it at a glance.

On the Data tab, click the Validation button, and select Show All Data Validations. The option will be checked, and you can uncheck it at any time if you don't want to view your validation rules anymore. If you don't have data validation in the selected document, a notification will appear to indicate this.

image alt text

All valid validation cells will appear with a green outline.

If you mouse-hover over a cell with a validation rule, it will turn green, and a tooltip will display what rule is being used within that cell, including custom rules you've assigned.

image alt text

If you have multiple cells using the same validation rule, when you mouse-hover over one, they will all turn green, so that you can see within a table all the locations where the rule is being used (this includes non-contiguous cells).

If you have a cell where the validation rule is being violated, the invalid cell will have a red outline. When you mouse-hover over an invalid cell, it turns entirely red.

Let's say you only want to view invalid cells, so that you can focus on correcting errors. You can do this by going to the Data tab, clicking the Validation button, and selecting Show Invalid Data Validations.

This will toggle your document to only show the invalid cells in red.