Data validation only applies to manual cell in Excel

When you apply data validation to either an individual cell or to a range of cells in your Microsoft Excel 2000, Microsoft Excel 98 Macintosh Edition, or Microsoft Excel 97 worksheet, Excel may accept invalid values despite your having applied data validation restrictions.

This behavior occurs when any one of the following conditions is true:

• You select a range of cells in which one of the cells has data validation restrictions. You then type an invalid data value, and press CTRL+ENTER. Excel inserts the invalid value that you typed into all the selected cells.
• You programmatically insert an invalid value into a cell in which you have applied data validation restrictions.
• You copy an invalid value from a cell and paste it into a cell in which you have applied data validation restrictions.
• You drag an invalid value from a cell to a cell in which you have applied data validation restrictions.
• You use the AutoFill feature to fill an invalid value into a cell in which you have applied data validation restrictions.
• You are using a Data Form.

NOTE: When you apply data validation restrictions on a cell, Excel only validates the data when you manually type the value in the cell. If you autofill, copy, or drag a cell without data validation restrictions, the formatting from the source cell will replace the destination cell's formatting. Therefore, the validation restrictions will be removed from the destination cell.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

With data validation, you can specify what data is valid for either an individual cell or a range of cells in the following ways:

• Create a list of entries that restrict the valid data values.
• Create a prompt that explains the type of data that is valid for a given cell.
• Display a message when incorrect data is typed.
• Check for incorrect entries using the Auditing toolbar.
• Set a range of numeric values that can be entered into a cell.
• Determine if an entry is valid based on a calculation in another cell.

For more information about data validation, click Microsoft Excel Help on the Help menu, type troubleshoot data validation in the Office Assistant or the Answer Wizard, and then click Search to view the topic.