Thursday, March 15, 2012

Data Validation in Excel

(in continuation of my earlier post: "Custom Validation in Excel")


Today I seriously recognised the power of data validation. I was stuck in a situation wherein I had to create a template wherein the user needs to enter a date, however it the date cannot be a Saturday or a Sunday. After a brief "R&D", I realized using "data validation" was the apt option.


Go to Data -> Data Validation


In the dialogue box which pops up, choose "Custom" from the drop down under Settings -> Validation Criteria -> Allow


In the new field "Formula", just enter the criteria you want to achieve. In my case, I used "=WEEKDAY(A2,2)<6"


Then, you may click on the "Error Alert" tab and may put any customized error alert like the picture below:




So, now the desired cell is validated and user can only enter a weekday.


Another, popular validation that I often use is the "List" wherein one can easily create a drop-down list

Just mention the options you want in your drop-down list under the "Source" field separating with "commas". In the picture above, the drop-down list would be list of alphabet from A to E.

Try experimenting further...! :-)

Note:
"Weekday" functions returns the serial number of the given date in a week.
Syntax:WEEKDAY(cell reference,number format)
Number Format Options:
1  :1 (Sunday) through 7 (Saturday)
2  :1 (Monday) through 7 (Sunday).
3  :0 (Monday) through 6 (Sunday).
11 :1 (Monday) through 7 (Sunday).
12 :1 (Tuesday) through 7 (Monday).
13 :1 (Wednesday) through 7 (Tuesday).
14 :1 (Thursday) through 7 (Wednesday).
15 :1 (Friday) through 7 (Thursday).
16 :1 (Saturday) through 7 (Friday).

No comments:

Post a Comment