How to set Data Validation rules in MS-Excel?

Asked By amity 220 points N/A Posted on - 06/07/2011

 I have a data entry project using excel, where user can enter valid data. I want some feature for my project that will make it more users friendly. I want my project to be more understandable with less supervision from me on what kind of data to be entered; such what kind of date format should be entered restrict them from entering invalid data to the cell.

For example, I may want to restrict data entry to a certain range of dates, limit choices by using a list, or make sure that only positive whole numbers are entered. Or something that will let the user enter invalid values to the cell but warn them from entering it and give instruction of what kind of data is needed for the cell. This will help the user and provide them more info regarding what kind of data should be entered. I don’t have idea if it possible so please lend me your knowledge with this.

 

• Status: Open
• Question Views: 950
• Answer Count: 2
• Vote Up 1 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by fawad ul hussan

Go To Solution

Best Answer

Best Answer

Answered By fawad ul hussan 0 points N/A #108240

How to set Data Validation rules in MS-Excel?

Dear you have to check the data validation for this kind of problem. The data validation allows you to set different things such that you have to go to your Excel worksheet then from the file menu choose the “Data” tab in this tab click on “Validation" now a dialog box will appear from where you can set your goal as desired. 

For example you want to choose " Text length " which will set the length for the text so you have to choose it now set the range for you data you want to set let say we want to set from "100" to "200" then you have to define it as maximum and minimum range and click " OK ". Now if you enter an invalid number, say 500, you will see the error message.

About fawad ul hussan

Questions
0

Answers
14

Best Answers
2

• Vote Up 0 Vote Down

• Posted on - 06/17/2011
• Question Category: MS Excel

Answered By chinaheart 0 points N/A #108241

How to set Data Validation rules in MS-Excel?

Data validation in MS Excel allows you to define what sort of information is being entered into a particular cell and can guide your users on how to use your worksheet.

At the main menu, choose Data>Validation and a dialog window will appear. Then in Settings tab, you can define the timeframe for dates and time, number within limits, texts of a specified length or provide a list where the user can simply choose among it. You can also specify whether the cell can be left blank or empty.

It can also warn the user if he or she is entering invalid information to the cell defining it at the Error Alert tab. However, if the user's data is copied, filled or calculated by a formula into the cell, the validation will not take effect. It means that it is only intended to prevent invalid entries when the user types the data into that particular cell.

Be careful though that you should also provide simple but clear instructions somewhere near the validated cell so that the user will not get confused or annoyed getting error messages.

I hope i was able to give you as much idea how data validation could work for your Excel project.

About chinaheart

Questions
0

Answers
1

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 06/30/2011
• Question Category: MS Excel