Excel Forum
Similar Content
Excel Data Validation - Limit What a User Can Enter into a Cell
Tutorial: Data Validation is a tool in Excel that you can use to limit what a user can enter into a...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a grea...
Ignore Blanks in a Data Validation List in Excel
Tutorial: I will show you 3 ways to remove the blanks from a Data Validation dropdown menu in Exce...

Excel Magic Trick #177: Data Validation Trouble

See how to format a column with the Text Number Format to avoid Data Validation Trouble.
Excel interprets 10-8895 as the date Oct, 8895
Excel interprets 0856 (if there is no custom format to allow leading zeroes) as 865
So if you have data validation based on those values, you can only use the drop down arrow to enter the values; you CANNOT type the values in
You can type the values in if you format the column as Text.
To apply Text Format: Ctrl + 1 opens the Format Cells Dialog Box, Number tab, then select Text
The keyboard shortcut for Data Validation is Alt + D + L (2003) or Alt A + V + V (2007)
Data Validation List Drop Down dropdown Arrow
Date Format Formatting
Number Formatting Format

Got a Question? Ask it Here in the Forum.