Microsoft Excel Training Videos

Data Validation in MS Excel

Data validation in Excel can help in the accurate and planned data entry which is the starting point for every good analysis of data later on. If you have 20 employees in a company, why not create a list of first and last names once and use them again and again without having to remember each name. Also the list can be edited once an employee leaves or a new one joins the team. Most companies have a policy on salaries and perks. The perks may be different for differently designated employees. Here again you can create a calculated least or percentages. You can now define the minimum and maximum salaries and also define that the person entering the data in the worksheet is warned if he mistakenly enters a salary lower than the lowest value or higher than the highest value. Another useful examle: Let's say you are working in the financial year 2009. Now you wish to ensure that only dates for the year 2009 and between 1st January and 31st December are entered. You can ensure this by using 'dates' validation and define appropriately in the data validation window. You can also define a maximum value for phone expenses per month or year and if this is exceeded in the total then Excel will warn you that you have already exceeded the budget! In this manner data validation can help plan your work and help avoid important mistakes.
In summary data validation helps the user as follows:
  1. You avoid errors during the entry of data in the Excel cells.
  2. The data entry is quicker.
  3. The validation can be applied to text, numbers, time, date and custom data.
  4. You can provide hints during the data entry so that validation criteria are not violated.
  5. If wrong data is entered then the data validation process warns the operator as shown in the Excel training video.

Further Reading

Data Validation in Excel