How To Restrict Data Input Using Data Validation

Sat 24th March 2012

An old saying from the early days of computing still stands true today - garbage in, garbage out. As efficient as Excel is, if a user inadvertently enters the word nine into a cell, instead of the number 9, it will not be recognised. Using data validation, you can restrict the types of data a user can enter into a chosen cell. Here are a couple of examples.

When George Orwell wrote his nightmare vision of the future, Nineteen Eighty-Four, he insisted that the title should always be written, and not converted into the numerical 1984. This stipulation was not wholly adhered to, and several versions of the book have appeared with the title printed as the year. The film of the novel, which was made in the year referred to in the title, appeared as 1984 too, possibly because it is a lot less cumbersome that the written title.

Sticklers for Orwell's preferred form of the title can strike a blow in Excel, however, by disallowing entry of the title if it is typed as a year. This is done using data validation, and the following demonstration will give an idea of how this useful feature of Excel works. Learn more in one of our data analysis courses UK.

Let us assume that the cell B3 asks the following question:

Winston Smith is the central character in which of Orwell's novels?

The user is invited to type the answer into cell C3, and it is this cell we will format. To ensure the user enters the title in full and not the numeric year, we could do the following.

Select the input cell (C3). Click on Data and select Validation from the list.

Select the Settings tab and click the arrow on the Allow menu to display the options. Select Text Length.

Click the arrow on the Data menu and select Greater than.

In the Minimum box, we enter 4. This will instruct Excel not to accept any entry into that cell that is under 5 characters long, including spaces. Doing this will exclude the numerical 1984 from the list. I should point out here, however, that this action will not exclude all numbers from the answer cell. If the user were to type in a five-or more digit value, it would be accepted as an answer. It is the number of characters we are restricting here, not the type of data.

Next, we could insert a prompt to inform the user that he or she must enter the answer as written words, and not as a numerical year. This may be a bit of a giveaway to the question, but it serves the purpose of this demonstration. To insert this prompt, we would click on the Input Message tab and in the Title box, type the word NOTICE. Then in the Input Message box we would type the following:

You must enter the title as the author intended.

Finally, we need to set up an error alert for those who try to enter the title as 1984. Click on the Error Alert tab and in the title box type the word ERROR. In the Error Message box, type the following:

You have entered an incorrect value.

You could have a little fun here, by entering a message like 'Were you not paying attention just there?', but all of this is a rather simplistic use of this function. Here is a more specific example of how to use data validation to limit entries to numeric values.

Let us assume you have a worksheet that requires input from users and one, in a moment of absent-mindedness, types in the word seven, rather than the numeric value. How would Excel treat this incorrect entry? You can see for yourself by entering a few random numbers into a column and inserting a written number somewhere in between. If you use AutoSum on the column, you will see that the written number is simply ignored.

It could be that this omitted value makes the difference between reaching a set target and failing to meet it. It is important, therefore, that any worksheet requiring numeric values only, should contain just that. To do this, we need to ensure that making an incorrect entry, such as the one above, becomes impossible, and once again this is achieved using data validation.

To demonstrate this, let us create a scenario where budding actors are invited to apply for the leading role in a forthcoming production of a play about a man facing a mid-life crisis. For the sake of authenticity, the actor must be between forty-five and fifty-five years old. Here's how Excel can weed out all of the unsuitable candidates using data validation.

In Excel 2003.

In the cell B2, type the question Age next birthday?

Select C2 to accept the answer, and choose Validation from the Data menu as before.

In the Data Validation dialog box, click the menu arrow for the Allow menu, and select Whole Number.

Open the Data menu, and select Between.

In the minimum and maximum boxes, enter 45 and 55 respectively.

You could also enter a message in the Error Alert tab. In the Title box, type SORRY, and in the Error Message box, you could put 'Your age does not match that of the lead character in this production. Click OK and that is that.

If you are using Excel 2007, select B3 as before and display the Data tab of the ribbon.

In the Data Tools group, Select the Data Validation tool from the Data Tools group to open the Data Validation dialog box.

Click the arrow to open the menu, and select Whole Number from the list.

Select Between from the Data drop-down list, and then click Between .

Enter the age boundaries in the Minimum and Maximum boxes. Open the Error message tab of the dialog box,

Enter an error message as above, and click OK.

Data validation is a very useful tool to have at your disposal when you want to control the data that will be entered by other users. There is a lot more to this feature than its uses in the examples given above, and learning how to utilise it effectively will set you on your way to becoming an Excel wizard. And ten out of ten looks good whether it's written in letters or numbers.

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

Original article appears here:
https://www.stl-training.co.uk/article-2064-how-restrict-data-input-using-data-validation.html