How to Control User Input Using Data Validation Part 1

How to control User Input Using Data Validatio Part 1 Feature Image

In this two-part series of blog posts, we are going to look at how to control data input by a user using the Data Validation feature of Excel. Data Validation limits the type of data that someone can enter into a cell. We’ll also look at how to display an Input Message when a user clicks into a cell and how to display Error Alerts if a user tries to input incorrect values.

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 also reduce the amount of work required for spreadsheet maintenance.

Using Data Validation in combination with effectively handling Excel error messages makes for a much better user experience and much less time spent on spreadsheet maintenance.

In this first blog post, we are going to look at one of the most common methods of Data Validation, a list, and include an Input Message, which will display when a user clicks into a cell, and an Error Alert, which will be displayed if a user inputs an incorrect value.

Where to Find Data Validation

To access the Data Validation dialogue, you need to click the Data tab on the ribbon, then click the Data Validation button as shown in the images below for Windows and Mac.

Selecting Data Validation - Windows

Selecting Data Validation – Windows

Selecting Data Validation - Mac

Selecting Data Validation – Mac

Create a List

Lists are one of the most commonly used types of Data Validation. A list is just a list of predefined options that a user can choose from. A list can be included in a dropdown menu. A dropdown menu is very good at making your spreadsheet look professional; it provides users with a valid list of choices to choose from, therefore preventing errors and speeding up the process of inputting data.

There are a few different ways of creating a list of values, including a delimited list, a reference to a range of cells, or a named range.

Using a Delimited List

Put simply, a delimited list is a list of values separated by a delimiter character such as a comma. For example, the list below is a delimited list of the days of the week:

 Monday, Tuesday, Wednesday, Thursday, Friday

Let’s create our first example. We are going to ask the user to input a type of fruit, and then use Data Validation to check the input against a delimited list of fruit. We’re not going to use a drop-down menu for this example, just to demonstrate that this option is possible.

First, click in cell B2, then click the Data tab on the Ribbon, then click the Data Validation button. Now, in the Allow: dropdown, select List from the drop-down menu.

List Option Allow Dropdown

Then type the following delimited list of fruit in to the Source: textbox and press enter or click OK.

Banana, Apple, Grapes, Grapefruit, Orange

Entering Delimited List

Now this is done, let’s type a fruit that isn’t in our list of fruit into cell B2 to see what happens. To do this, type Avocado in cell B2 and press enter

Data Validation Error Message

When you do this, you receive an error message similar to the image above.

There you go, Data Validation in action; you can only enter values from our list. Just to prove you can enter a fruit from our list, now type Grapes. Note that Data Validation is case sensitive, so it has to be Grapes and not grapes.

Data Validation Valid Value in a List

In-cell Dropdown

Ok, the previous example demonstrates the point that you can only enter a value from a pre-defined list. However, it might be frustrating to the user if they don’t know what fruit are on our list and therefore a valid entry. So, to improve the user experience, let’s add a dropdown list of fruit that the user can choose from.

Click in cell B2 again, then click the Data tab on the Ribbon, and then click the Data Validation button. The Data Validation dialogue should appear as before, with our delimited list of values.

Delimited List of Values

This time, select the In-cell dropdown option, as shown below, and press Enter or click OK.

In-cell Dropdwon Checkbox

Now, next to cell B2, you should see a dropdown menu icon that looks like an arrow pointing downwards.

Dropdown Menu Icon

When you click the icon, you will see the fruit on our list appear in a dropdown menu. Simply click the fruit you want to select in order to enter it into the cell.

Select Item from Dropdown Menu

This is a much quicker and more user-friendly way to input a value. If you prefer, you can still type a value into the cell directly.

Using a Range of Cells

Using a delimited list of values typed into the Data Validation dialogue works just fine, but it’s not great from a maintenance point of view. Imagine you are using the same list of values in more than one dropdown menu. If you want to change one of the values due to a mistake, you would need to go to each cell and change the values. For instance, what if one of our types of fruit is out of stock and you don’t want people to be able to select it?

A better approach is to use a reference to a range of cells. To do this, we are going to input our list of fruit into the range of cells A6:A10.

Range of Cells to be Referenced

Now, click on cell B2, click the Data tab on the Ribbon, then click the Data Validation button. When the Data Validation dialogue appears, change the text in the Source: textbox to =A6:A10 and press Enter or click OK.

Data Validation Source Textbox Cell Refernce

Now, the dropdown should work in the same way.

Data Validation dropdown menu using a cell reference

If we want to change one of the fruits in our list, we can simply select the appropriate cell and change the value. For instance, if we want to replace Grapefruit with Avocado, we simply type Avocado into cell A9 and press enter.

Data Validation Replace Grapefruit with Avocado

Now, if we select our dropdown menu again, we see Avocado in the dropdown menu.

Avocado in Data Validation Drop Menu

This would also be true of any other Data Validation list that are referencing the same range of cells.

Using Named Ranges

Using a reference to a range of cells is better than a delimited list. Better still is a Named Range. If you want to add new values rather than just change the values in your list, you still need to go back to each cells Data Validation dialogue and update the cell references. However, if you used a Named Range, you can get around this.

If you are not certain what a Named Range is, click here to read my blog post.

Let’s create a Named Range. Where possible, I like to keep my Named Ranges on a separate worksheet, so, selecting a new worksheet, type our list of fruit into cells A1:A5.

List of Fruit to be Used in Named Range

Now, select the range of cells A1:A5, then click in the Name Box, type the word Fruit, and press Enter.

Creating Fruit Named Range

Going back to the worksheet with our example, click on cell B2, click the Data tab on the Ribbon, and then click the Data Validation button. When the Data Validation dialogue appears, change the Source textbox to =Fruit and press Enter or click OK.

Add The Named Range to the Data Validation Source Textbox

Our dropdown menu works just as before.

Data Validation Dropdwon Menu Using The Named Range Fruit

Now, to add a new fruit to our list, go back to the worksheet and add Avocado to cell A6. To update our named range we going to use the Name Manager dialogue on Windows, or Define Name dialogue on the Mac.

Start by clicking the Formulas tab on the ribbon, and click either Define Name on the Mac, or Name Manager on Windows, as shown in the images below.

Select Define Name Dialogue Mac

Open the Define Name Dialogue – Mac

Select the Name Manager Dialogue - Windows

Open the Name Manager Dialogue – Windows

The Define Name dialogue on a Mac or Name Manager dialogue on Windows should appear. From here, you can select the named range Fruit and change the cell reference to A1:A6 as show in the images below.Once this done, press Enter or click OK.

Change Named Range Using Define Dialogue - Mac

Change Named Range Cell Refence Using Define Dialogue – Mac

Change Named Range Cell Reference Using Name Manager - Windows

Change Named Range Cell Reference Using Name Manager – Windows

The named range has now been updated.

To prove this, if we click in cell B2 on the worksheet with the dropdown menu, we should see that the list now has six choices.

Avocado Added to Dropdown Menu

All dropdown menus that reference this Named Range will also have been updated, without any need to open a Data Validation dialogue menu.

Excel provides a tool specifically for managing Named Ranges. To find out more, read my blog on the Name feature by clicking here.

Ignore Blanks

On the Data Validation dialogue, you may have noticed a checkbox with the title Ignore Blanks.

Data Validation Ignore Blank Checkbox

The title can be a little misleading. If you have a dropdown list that references a range of cells either using a cell reference or a Named Range, and that range contains a blank cell, this tells Excel to except values that aren’t on your list. For example, let’s add a blank cell to our named range Fruit by inserting a new row in the middle of the list so it looks like the image below.

Blank Cell Inserted in Named Range

Now, if we click on the dropdown list in cell B2, we will see a blank cell in the options that we can choose.

Blank Cell Added to Data Validation Dropdown Menu Options

Make sure that the Ignore blank check box on the Data Validation dialogue is checked.

Ignore Blank Checkbox Checked

Now, if we click on cell B2 and type anything we like, for instance, Melon, and then press enter, Excel will allow this input.

Data Validation Allows Values Not on the List

Go back to the Data Validation dialogue and now uncheck the Ignore blank check box.

Ignore Blank Checkbox Unchecked

Delete the word Melon from cell B2, and try to enter it again. This time, you will receive an error message.

Data Validation Ignore Blank Checkbox Unchecked

So, if you have blank cells in your source list of values, and you have the Ignore blank check box on the Data Validation dialogue checked, you can enter any value. Personally, I think this option is counter-intuitive, and I would try to avoid having blank cells in your source list of values.

Input Message

Okay, so, we’ve created a list of values for our users to choose from. It would be useful to give our users some instructions on how to select values. Excel Data Validation can help us with this by using an Input Message.

To create an Input Message, simply click on cell B2, and bring up the Data Validation dialogue. Now, select the tab entitled Input Message.

Data Validation Input Message

Make sure that the Show input message when cell is selected checkbox is checked; otherwise, the Input Message won’t be visible to the user. Now, let’s give our message a title by clicking in the Title: textbox and typing, Example Input Message.

Data Validation Input Message Title

Next, click in the Input message: textbox, type, Please select a fruit from the list as our input message, and click OK.

Data Valdiation Input Message Text

Finally, when the user clicks on cell B2, a message box is displayed with Input Message Title in bold text, with the Input Message below.

Data Validation Input Message Displayed

The user can select an option from the list, just like before. We use the input message to provide some context to the options available.

Error Alerts

In the final part of this blog post, we are going to look at the different types of Error Alert that can be displayed to a user when they input an invalid value.

Excel’s Data Validation gives us the option to choose between three different types of error alerts: Stop, Warning, and Information.

To create an Error Alert, click the third tab, Error Alert, on the Data Validation dialogue.

Data Validation Error Alert Tab

Just as with the Input Message, we have to provide a Title for our Error Alert and some Error Message text that will be displayed to the user.

Stop Message

The Stop Message basically prevents users from entering an incorrect value by giving them two options when it detects an invalid input: Retry or Cancel.

Let’s create our first Error Alert, click in cell B2, and bring up the Data Validation dialogue. Now, select the tab entitled Error Alert. From the Style: dropdown menu, select the Stop option.

Select Stop from Data Validation Error Alert Style Dropdwon Menu

Give the Error Alert a title by clicking in the Title: textbox and typing, Stop Message. Now, click in the Error message: textbox and type, You’ve input an invalid fruit as the error message, and click OK.

Data Validation Stop Message Title and Text

Click in cell B2, and type anything that isn’t on our list, for example, Tomato, and press enter.

Data Validation Stop Message Displayed

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 has been entered.

 Warning Message

The Warning Message also presents the user with two options, Yes and No. If a user enters an invalid entry, they can either select Yes to accept the input, or No to return to the cell and highlight the text they have input. This can be useful if you want to accept items that aren’t part of the list.

To create a Warning Message, click in cell B2, bring up the Data Validation dialogue, and select the tab entitled Error Alert. From, the Style: dropdown menu, select the Warning option.

Data Validation Error Alert Warning Style

Give the Error Alert a title by clicking on the Title textbox and type Warning Message. Now, click on the Error message textbox, type You’ve input an invalid fruit as the error message, and click OK.

Data Validation Warning Message Title and Text

Again, click on cell B2, type anything that isn’t on our list, for example, Tomato, and press enter.

Data Validation Warning Message Displayed

When you press enter, a Warning Message appears with two options: Yes and No.

 Information Message

The final Error Alert is the Information Message. This simply accepts the user input but displays a message to the user with a single option to click OK.

Like the previous error alerts, to create the Information Message, click on cell B2, bring up the Data Validation dialogue, and select the tab entitled Error Alert. From the Style: dropdown menu, select the Information option.

Data Validation Error Alert Information Style

Give the Error Alert a title by clicking on the Title textbox and type Information Message. Now, click on the Error message textbox, type You’ve input an invalid fruit as the error message, and click OK.

Data Validation Information Message Title and TextOne last time, click in cell B2 and type anything that isn’t on our list, for example, Rhubarb, and press enter.

Data Validation Information Displayed

When you press enter, the Information Message appears with the single option to press OK.

Wrap-up

Ok, that’s it for this blog post. In this blog post, we had a quick introduction to using Excel Data Validation to control user input. You’ve seen how to create a drop-down list, display, and Input Message to the user when they click on a cell to help choose the right option and three different types of Error Alerts that can be displayed to a user when they input an invalid value.

In the next part of this blog post series, we will take a look at different types of validation criteria including Whole Numbers, Decimal, Date, Time, and Text Length.

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