Concept of Data Validation

Concept of Data Validation?
Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The validation happens once the user enters data and hits the return key (enter key).


Why should I use data validation?


Data validation, though a simple tool, can be of lots of help. It’s very core function (of validating data) can be applied to many other tasks. Here are a few things that can be done using data validation tool:
  • The very basic function of data validation is to validate the data. This tool can be used to get correct data from the users. It can also be used to get desired data, by the way of limiting the cell input using custom input
  • In large shared spreadsheets, which are updated real-time by more than one user, certain columns can have restricted data entry. This can limit the incorrect entries to the cells
  • Keeping a check on inconsistencies. This can be achieved by creating a list of certain words, which go in a certain range of cells and these can be suggested to users by giving them an option to select from drop-down menus. These in-cell drop down menus can be created using data validation
  • Custom error / alert / input messages can be displayed to users after or before a cell entry is made. Error messages are shown when an incorrect entry is made. The input message is shown when a certain cell is selected, where the input is required. On selection a message can be displayed which can show instructions or warnings etc.
  • In-cell drop down menus. This is one of the most useful and my favorite uses of data validation
What validations can be applied to the cells?
As I told earlier the cells entries can checked for many types of validations. For example, I can restrict a certain range of cells to accept only numeric value; I can also limit the range within which the cell values can be input. I can also restrict the cells to accept only date values or time values, etc.

Here is a detailed explanation of what exactly you can do with this tool:
Validations: The first tab of the data validation tool is where you can define you validation rules. These are the following rules that can be applied on the cell or range of cells –

  • Whole numbers: You can use this option in case you want the cell to accept only whole numbers as input from the users. You can also define certain limits to the whole numbers also, such as minimum and maximum limits of the range, which is acceptable, other rules such as equal to, more than, less than, not equal to, etc.
  • Decimal numbers: This option allows you to restrict the cells to accept numeric values, which can also have decimal values. The difference between this option and the previous option is that, in previous option only whole numbers could be input and with this option you can allow decimal numbers also. You can apply same rules on the decimal values as you can apply on whole numbers.
  • Date / Time: These are 2 different options with which you can restrict the cell entries to be either date or time value. Again, on these options also you can apply equal to, not equal to, more than, etc. rules
  • Text Length: This option restricts the cells to contain only a limited length of text. The text here includes all valid characters in Excel.
  • List: This option allows you to validate the cell entry with a pre-defined list. You can define your list, with words or phrases, which you wish should only be entered in the cells. This option allows you to provide the user as in-cell drop down menu with which one can select a particular item from the list. This way you can restrict the data input in the cell together with giving the user a drop down list of required items.
Message Alerts / Error Alerts: This are alerts which displayed to users once either the user clicks on the cell, which has data validation active, or enters invalid data (as per the validation rules). Through error alerts you can either give a ‘Stop’ message or ‘Warning’ message or simply an ‘Information’ message to alert the user about the type of message input.


How to apply data validation?
The following steps will guide how one can apply data validation to a range of cells. The following steps can be applied in Excel 2003 and Excel 2007 as well:

  • Select the cell or range of cells to which data validation is to be applied
  • Go to 'Data' menu and then click on 'Validation'. The Data validation wizard will open up
  • On the validation tab, click on the drop down menu, where currently 'Any Value' is shown. Currently, the cell can accept any value
  • You can choose from the various validations that are available in the drop down list
  • Once you select the validation type, another drop down list will show up (depends on which option you selected on previous step) which will list the various rules that can be applied on the validations
  • In case you would like to activate Alert or Error messages, those can be defined in the next tabs
In the below image, I have applied validation for whole numbers, between the range of 0 to 100.





Note: Though data validation is one of the very useful tools in Excel, it also could not keep itself from some limitations. One of the major limitation (or call it a bug) is that it does not work when some data is pasted using CTRL+C and CTRL+V. When some data is pasted on the cells, which have validation, the cell entries are not validated.

If you are familiar with the concept of Data Validation or have worked with this concept after reading this post, I would like to know about it. Share your experience using data validation tool with us, issues you faced and how did you solve it.
Share on Google Plus

About Nifty Guru

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.
    Blogger Comment
    Facebook Comment

5 comments:

  1. Awesome post..properly structured..with good amount of details.

    ReplyDelete
  2. Great tips , thank you,
    Excellent blog, nice post. I love the contents and thoughts. Keep writing.. I am so proud to vote for this beautiful blog. I VOTED FOR YOU, I recommend your blog to many of my friends. They too Voting for this blog. I hope you will grace me with your precious Vote.Just copy this link to your browser
    http://bloggerschoiceawards.com/blogs/show/69551

    ReplyDelete
  3. Is it free of charge ? or ?!
    Giorgio

    ReplyDelete
  4. @Awesome
    Thanks for the feedback buddy

    @collegegirl
    Thanks to you too.

    @GCinci
    Giorgio, i didn't get what exactly you are asking about. Are you asking about using this blog? If yes, then Yeah, the blog is free to use. In case you are asking about anything else, please let me know. I'll be happy to help.

    ReplyDelete

Saturday, July 4, 2009

Concept of Data Validation

Concept of Data Validation?
Data Validation is a tool provided by MS Excel that allows you to validate data that is entered by a user. The validation happens once the user enters data and hits the return key (enter key).


Why should I use data validation?


Data validation, though a simple tool, can be of lots of help. It’s very core function (of validating data) can be applied to many other tasks. Here are a few things that can be done using data validation tool:
  • The very basic function of data validation is to validate the data. This tool can be used to get correct data from the users. It can also be used to get desired data, by the way of limiting the cell input using custom input
  • In large shared spreadsheets, which are updated real-time by more than one user, certain columns can have restricted data entry. This can limit the incorrect entries to the cells
  • Keeping a check on inconsistencies. This can be achieved by creating a list of certain words, which go in a certain range of cells and these can be suggested to users by giving them an option to select from drop-down menus. These in-cell drop down menus can be created using data validation
  • Custom error / alert / input messages can be displayed to users after or before a cell entry is made. Error messages are shown when an incorrect entry is made. The input message is shown when a certain cell is selected, where the input is required. On selection a message can be displayed which can show instructions or warnings etc.
  • In-cell drop down menus. This is one of the most useful and my favorite uses of data validation
What validations can be applied to the cells?
As I told earlier the cells entries can checked for many types of validations. For example, I can restrict a certain range of cells to accept only numeric value; I can also limit the range within which the cell values can be input. I can also restrict the cells to accept only date values or time values, etc.

Here is a detailed explanation of what exactly you can do with this tool:
Validations: The first tab of the data validation tool is where you can define you validation rules. These are the following rules that can be applied on the cell or range of cells –

  • Whole numbers: You can use this option in case you want the cell to accept only whole numbers as input from the users. You can also define certain limits to the whole numbers also, such as minimum and maximum limits of the range, which is acceptable, other rules such as equal to, more than, less than, not equal to, etc.
  • Decimal numbers: This option allows you to restrict the cells to accept numeric values, which can also have decimal values. The difference between this option and the previous option is that, in previous option only whole numbers could be input and with this option you can allow decimal numbers also. You can apply same rules on the decimal values as you can apply on whole numbers.
  • Date / Time: These are 2 different options with which you can restrict the cell entries to be either date or time value. Again, on these options also you can apply equal to, not equal to, more than, etc. rules
  • Text Length: This option restricts the cells to contain only a limited length of text. The text here includes all valid characters in Excel.
  • List: This option allows you to validate the cell entry with a pre-defined list. You can define your list, with words or phrases, which you wish should only be entered in the cells. This option allows you to provide the user as in-cell drop down menu with which one can select a particular item from the list. This way you can restrict the data input in the cell together with giving the user a drop down list of required items.
Message Alerts / Error Alerts: This are alerts which displayed to users once either the user clicks on the cell, which has data validation active, or enters invalid data (as per the validation rules). Through error alerts you can either give a ‘Stop’ message or ‘Warning’ message or simply an ‘Information’ message to alert the user about the type of message input.


How to apply data validation?
The following steps will guide how one can apply data validation to a range of cells. The following steps can be applied in Excel 2003 and Excel 2007 as well:

  • Select the cell or range of cells to which data validation is to be applied
  • Go to 'Data' menu and then click on 'Validation'. The Data validation wizard will open up
  • On the validation tab, click on the drop down menu, where currently 'Any Value' is shown. Currently, the cell can accept any value
  • You can choose from the various validations that are available in the drop down list
  • Once you select the validation type, another drop down list will show up (depends on which option you selected on previous step) which will list the various rules that can be applied on the validations
  • In case you would like to activate Alert or Error messages, those can be defined in the next tabs
In the below image, I have applied validation for whole numbers, between the range of 0 to 100.





Note: Though data validation is one of the very useful tools in Excel, it also could not keep itself from some limitations. One of the major limitation (or call it a bug) is that it does not work when some data is pasted using CTRL+C and CTRL+V. When some data is pasted on the cells, which have validation, the cell entries are not validated.

If you are familiar with the concept of Data Validation or have worked with this concept after reading this post, I would like to know about it. Share your experience using data validation tool with us, issues you faced and how did you solve it.

Concept of Data ValidationSocialTwist Tell-a-Friend

5 comments:

Anonymous,  July 9, 2009 at 11:22 PM  

Awesome post..properly structured..with good amount of details.

collegegirl July 10, 2009 at 11:27 AM  

Great tips , thank you,
Excellent blog, nice post. I love the contents and thoughts. Keep writing.. I am so proud to vote for this beautiful blog. I VOTED FOR YOU, I recommend your blog to many of my friends. They too Voting for this blog. I hope you will grace me with your precious Vote.Just copy this link to your browser
http://bloggerschoiceawards.com/blogs/show/69551

GCinci July 13, 2009 at 3:14 PM  

Is it free of charge ? or ?!
Giorgio

Mohit Khurana July 13, 2009 at 10:31 PM  

@Awesome
Thanks for the feedback buddy

@collegegirl
Thanks to you too.

@GCinci
Giorgio, i didn't get what exactly you are asking about. Are you asking about using this blog? If yes, then Yeah, the blog is free to use. In case you are asking about anything else, please let me know. I'll be happy to help.

Post a Comment