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.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.