How to Control User Input Using Data Validation Part 2

In this second part of a two-part series of blog posts on Excel Data Validation, we are going to look at how to control data input by users to whole numbers, decimal numbers, date, time, and text length. We’ll also look at how you can set minimum and maximum limits on the values input by users.

You can download the example spreadsheets used in this blog post by clicking the button below or by clicking the link here.

Quick Recap

Quick recap of what was covered in part-one in this series of blog posts. Excel provides several methods of Data Validation for limiting data input, such as dropdown lists, limiting to whole numbers, decimal numbers, and dates in a certain range, to name a few. Data validation is extremely useful for controlling user input, which in turn can reduce spreadsheet errors, improve data quality, and reduce the amount of work required for spreadsheet maintenance.

In the first blog post, we looked at one of the most common methods of Data Validation, a list. We also looked at how to include an Input Message, which is displayed when a user clicks into a cell. We also looked at how to create an Error Alert, which is displayed when a user inputs an incorrect value.

If you haven’t already read part-one of this series of blog posts, you can do so by clicking here.

Whole Numbers

Let’s start with whole numbers. A whole number is a number with no numbers after a decimal point; for example, the numbers 1, 2, 5 and 10 are whole numbers, but 1.1, 3.5 and 7.4 are decimal numbers.

The title Excel uses here is a bit misleading because whole numbers shouldn’t include negative numbers. However, you can specify negative numbers as a valid value in the whole number Excel Data Validation option. Whole numbers that include negative numbers are referred to as integers. For more information on the difference between whole numbers and integers, please click here.

Okay, let’s look at an example. Imagine you are collecting feedback on a course, and you want participants to rate the course on a scale of 1–5.

To do this, we would first need to access the Data Validation dialogue by clicking in cell B2 (the cell we want to collect the results in). Then, click the Data tab on the Ribbon, then click the Data Validation button.

Selecting Data Validation - Mac

Selecting Data Validation – Mac

Selecting Data Validation - Windows

Selecting Data Validation – Windows

Now, in the Allow: drop-down, select the Whole number option from the drop-down menu.

Select Whole Number

Then, in the Data: dropdown, select the between option from the drop-down menu.

Select between

When this is done, you should see two text boxes, Minimum: and Maximum:. In the Minimum: textbox, enter 1 and in the Maximum: textbox, enter 5. Once this is done, press Enter or click the OK button.

Enter Minimum 1 and Maximum 5

Now, if we choose to enter 3 as the rating in cell B2, the value should be accepted.

Value 3 Accepted

However, if you try to enter a value greater than 5 or less than 1, you will receive an error. For example, try entering the value 7.

Whole number Error Message

You will receive an error message like the image above. If you like, you can create a more user-friendly Error Alert, as discussed in part-one of this series of blog posts.

Let’s create an Error Alert. Click in cell B2, and bring up the Data Validation dialogue. Now, select the tab entitled Error Alert.

Error Alert Tab Data Validation Dialogue

From the Style: dropdown menu, select the Stop option.

Stop Style Error Alert Data Validation

Give the Error Alert a title by clicking in the Title: textbox and typing, Input Error. Now, click in the Error message: textbox and type, You’ve input an invalid rating. Please enter a value between 1-5 as the error message and click OK.

Error Alert Title and Error Message Data Validation

Click in cell B2, type 7, and press enter.

Whole Number Error Alert Data Validation

When you press enter, the Error Alert dialogue appears with two options: Cancel and Retry.

If you click the Cancel button, it will clear the invalid entry from cell B2; if you click the Retry button, it will return you to cell B2 and highlight the text that was entered.

Decimal

Next, we look at the decimal numbers Data Validation option. Data Validation for decimal numbers works in pretty much the same way as whole numbers, but this time, you can enter decimal numbers such as 1.1, 3.5 and 7.4.

For our second example, imagine we want users to enter water temperature; we only want to allow users to enter numbers greater than zero, including decimal numbers, because water freezes at 0 degrees Celsius.

To do this, we access the Data Validation dialogue by clicking in cell B2 (the cell in which we want to collect the results). Click the Data tab on the Ribbon; then, click the Data Validation button.

Now, in the Allow: dropdown, select the Decimal option from the drop-down menu.

Select Decimal Data Validation

Then, in the Data: dropdown, select the greater than or equal to option.

Select Greater Than or Equal to Data Validation

When this is done, you should see one textbox, Minimum:. In the Minimum: textbox, enter 0.

Minimum Value 0 Data Validation

Once this is done, press Enter or click the OK button. Now, if we choose to enter 3.1 as the water temperature in cell B2, the value should be accepted

Value 3.1 Entered and Accepted Data Validation

However, if you try to enter a value less than 0, for example –4.5, you will receive an error.

Decimal Error Message Data Validation

Again, you can create a more user-friendly Error Alert, as discussed in part-one of this series of blog posts.

Date and Time

For our third example, we are going to allow users to input a start time for a meeting, except during lunchtime (12:00-13:00).

Again, we need to access the Data Validation dialogue by clicking in cell B2, (the cell we want to collect the results in). Then, click the Data tab on the Ribbon, and then click the Data Validation button.

Now, in the Allow: dropdown, select the Time option from the drop-down menu.

Select Time Option

Then, in the Data: dropdown, select not between from the drop-down menu.

Select Not between Option

When this is done, you should see two textboxes, Start time: and End time:. In the Start time: textbox, enter 12:00 and in the End time: textbox, enter 13:00. Once this is done, press Enter or click the OK button.

Entering Start Time and End Time

Now if we choose to enter 14:30 as your meeting start time in cell B2, the value should be accepted.

Time 14:30 accepted in Cell B2

However, if you try to enter a value between 12:00 and 13:00, you will receive an error. For example, try entering the value 12:30.

Time Error Message

You will receive an error message, but as mentioned previously, you can create a more user-friendly Error Alert as discussed in part-one of this series of blog posts.

The same principle used for the Time example above applies to Dates. While I don’t provide a date example in this blog post, the accompanying example spreadsheet has a date example.

Text Length

For our final example, we are going to look at limiting the length of text input by a user. This could be a useful limit input such as addresses.

Access the Data Validation dialogue by click in cell B2 (the cell we want to collect the results in). Then, click the Data tab on the Ribbon, then click the Data Validation button.

In the Allow: drop-down, select the Text length option from the drop-down menu.

Select Text Length Option Data Validation Dialogue

Then, in the Data: drop-down, select the less than or equal to option from the drop-down menu.

Select Less Than or Equal to Option

When this is done, you should see one textbox, Maximum:. In the Maximum: textbox, enter 50. Once this is done, press Enter or click the OK button.

image-24-max-50

Now, if we choose to enter your hometown as your place or residence, it should be accepted, so long as it contains no more than 50 characters. So, for example, we could enter Melbourne.

Enter Melbourne

 

However, if you try to enter a text string greater than 50 characters, you will receive an error message

Text length Error Message Data Validation

As mentioned previously, you can create a more user-friendly Error Alert, as discussed in part-one of this series of blog posts.

Wrap-up

Okay, that’s it for this blog post and this series of blog posts on Excel Data Validation. In this blog post, you’ve seen how you can limit user input to whole numbers, decimal numbers, time, dates, and text length. You’ve also seen how to limit the values input by the user, to between, greater than, less than, and not between a range of values.

That’s it for this post.  If you’d like a copy of the spreadsheet used in this post, please sign up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit