|
|
Featured Tutorial
Featured Macro
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
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