Data Validation In Excel

Wed 23rd March 2011

Data validation in Excel allows error trapping of wrongly added data as it is being entered rather than later when calculations and formula are affected. Find out how to use Data Validation in Excel and help protect your data entry.

If you're entering lots of data into Excel or perhaps someone else is doing this for you, then Data Validation can be a really useful feature to use in Excel. This article covers what data validation is and how it can be used effective data entry.

The purpose of Data Validation in Excel is to catch data entry errors before the data entry is complete, rather than afterwards. So the user is made to enter the correct data as it is being entered. It's often used in data entry parts of a worksheet and you want to ensure the data entered is the correct type and where appropriate within the correct range.

What is Data Validation?

Excel's Data Validation feature checks data as it's being entered. You can set validation to allow particular kinds of data, for example, only numerical data within a certain range. Then if a user types in the wrong data type, for example text rather than a number, or a number outside the required range, the data is blocked and an alert message warns the user. You can choose different types of alert warnings, such as to always block the data and make the user retry, or warn the user and give them the choice to retry or continue, or just alert the user and allow them to continue.

To use Data Validation in Excel you first highlight the required cells then apply Data Validation to the cells. You can then select the data type or range you want to allow, and you can also set the kind of alert the user will see if they enter the wrong data. When you set validation details you can also add a label which the user will see one of the cells is selected. The input label might say, for example, "Please enter numbers only". You can also add your own text to the alert prompt should the user type in the wrong data. So rather than setting the alert text as "Wrong you silly person!!!" you could set the alert text as "Wrong data type, please enter numbers only", so your alert text guides the user as to the correct data entry.

How to setup Data Validation

Suppose you have a section of an Excel worksheet where you want a user to enter a table of data. For example say we have a table used for entering staff details. Table headings include Firstname, Surname, Date Joined and Salary. Because each table column will hold different data types, we would usually set data validation for each column separately. However you can set validation on more than one column at a time for common data types. In this example we'll set data validation for the salary cells. Don't forget salary data is just numerical data formatted as currency. So first of all set all cells under the Salary heading to currency format.

Now we'll set Data Validation of these salary cells to numbers between 10,000 and 30,000. We'll pretend that no member of staff earns a salary outside this range, so if a salary of £40,000, or £8,000 is entered by mistake we want to alert the user and correct the error immediately. Ensure all the salary cells under the Salary heading are selected. To set validation in Excel 2003 choose Data, Validation. In Excel 2007/2010 choose the Data tab, Data Validation. The Data Validation panel appears. This panel has three tabs; Settings, Input Message and Error Alert. We're going to look at each tab in turn.

Settings Tab: This tab lets you choose a data type and a range if appropriate. Select this tab and under "Allow" click the pop down and choose Whole Number. Then in the lower part of the panel in the Minimum box type in 10000, and in the Maximum box type in 30000. This sets validation to only allow whole numbers between 10000 and 30,000.

Input Message Tab: This tab lets you enter a message the user will see when one of the cells is selected. Select this tab and choose the "Input Message Box" and type in the title "Salary" and Input Message "Please enter salary between 10,000 and 30,000".

Error Alert Tab: This tab lets you choose from one of three kinds of alerts and you can add your own text alerts. Select this tab then choose the Error Alert tab. You'll see that the Style pop down has three options. Choosing Stop will block incorrect data and make the user try again; choosing Warning with prompt the user with an alert but allow the option to continue; choosing Information will alert the user that the data does not match but will allow it. In this example we'll leave the option at Stop. In the Title box type "Error error!" and in the "Error Message" box type "Wrong data type or out of range. Please enter salary between 10,000 and 30,000". Then click OK to finish. Finish by clicking off the cells to remove the highlight.

To test the validation settings, select one of the salary cells. You'll see the input message. Try entering a salary outside the required range and you'll see the alert message. The alert allows you to cancel or retry.

You might like to experiment with the different data types you can validate. You change the data type in the Settings tab by choosing another of the options under "Allow". You can choose from Whole Number, Decimal, List, Date, Time, Text Length, Custom.

A really useful way to learn more about Excel's Data Validation features is to attend a training course and this will also give you the opportunity to find out lots more about Excel's many features.

Author is a freelance copywriter. For more information on excel+training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1554-data-validation-in-excel.html