Leading the way in Microsoft Office Development

Excel

What is Data Validation ?

The main purpose of data validation is to create restrictions on the type of data that can be entered in a cell.

Very useful for preventing the entry of incorrect data or data that does not meet a particular criteria.

Data Validation only restricts the data that is entered - the cells can still be deleted or pasted over.

Data Validation is a way to validate your data without doing any actual programming.

It is possible to copy and paste invalid data into cells with data validation ??

What can you use Data Validation for ?

1) For restricting the type of data that can be entered in a particular cell.

2) To provide a drop-down list of values that can be entered in a particular cell.

3) Displaying a descriptive prompt to remind users what type of information should be entered in a cell.

4) Displaying an error message if the user enters an invalid value in a cell.

5) Prevent a value appearing more than once.

6) numbers within a specific range.

7) a certain number of characters to be entered.

8) restricts entries to a specific type or size.

Using Data Validation

This can be found by selecting (Data > Validation).

Data Validation can be very useful for setting up worksheets which will be used by other users.

This allows you to specify conditions which must be meet for the input data to be accepted.

Once data has been defined for a particular cell(s) it is not possible to link the cell to another cell that contains invalid data.

Cell References

If you use any relative cell references in your criteria then these will be automatically shoft to reflect the active cell.

To guarantee that you always get the expected results you should always try and use absolute cell references.

Removing Data Validation

Removing Data Validation

Copying Data Validation

You can easily copy validation criteria from one cell to another using the normal (Edit > Copy / Paste)

If you want to only paste the validation select (Edit > Paste Special)

SS

Finding Data Validation

To quickly find all the cells that have data validation applied

(Edit > GoTo)

Special, Data Validation

ALL

Press OK

Things to Remember

If you copy a cell and paste it over a cell that has Data Validation applied to it then the validation will be lost without a prompt.

You can copy data validation from one cell to another by using the (Edit > Copy) and (Edit > Paste Special) dialog box.

If you want to apply data validation to a large block of cells, it is easier to add the data validation to just one cell and then copy and paste the data validation using the Paste Special dialog box.

These rules only apply to data that is entered into a cell manually and does not work on data that is pasted into cells using a macro ??