Data Validation in Excel allows you to control what the users can enter in a cell. Learn to use Data Validation and even create a drop-down list with it.

What Is Data Validation in Excel?

The Data Validation feature in Excel allows you to specify what kind of data the user may enter in a cell. With Data Validation, you can create a drop-down list of your values for the user to choose from, or set a minimum and maximum value for the data they can enter, and much more.

Data Validation in Excel also allows you to display alerts and input messages to inform the user of what they can enter in a given cell. Data Validation is simple in nature, but you can also use it to validate user-input with your own custom formulas.

How to Use Data Validation in Excel

The basic of using Data Validation is that you select the cell(s) you want to validate data for, and then create a Data Validation rule for them. To bring up the Data Validation dialogue:

  1. Select your cell(s).
  2. From the Ribbon, go to the Data tab.
  3. Click on Data Validation.

Microsoft Excel offers plenty of options to choose from, so before we start, let's have a quick glance at what you'll be working with.

Choosing From the Data Types

In the Data Validation dialogue, the first tab is labeled Settings and contains the validation criteria. This is where you'll be creating the data validation rule.

You can control what kind of data your cell will be accepting by choosing it from the drop-down list under Allow.

Microsoft Excel offers plenty for you to choose from (such as date, time, text, whole number, list, and more). However, you can also use your own formula by selecting Custom from the drop-down list.

There are numerous data types you can choose from or just add your own custom formula.

The second part of the Settings tab is defining extremums and deciding how the Data Validation rule should behave regarding this extremum.

For instance, after selecting between, you can select a minimum and maximum, and if the user enters invalid data, Excel won't accept it.

Adding an Input Message

You can add an Input Message to inform the user of the Data Validation rules with a custom message when they select the cell. Adding an input message is optional, but it's good practice to add one so that the user knows what the cell is anticipating.

Adding an input message is a good idea.

Creating an Error Alert

In the Error Alert tab, you decide how the data validation rule is imposed. There are three alert styles you can choose from. These styles also have different functions:

Stop: This alert style prevents the user from entering invalid data in the cell. The user can attempt again and enter valid data.

Warning: This alert style warns the user that the data they entered is invalid, but still allows them to enter it. The alert window offers three choices to the user: Yes (to enter the invalid data), No (to edit the invalid data), and Cancel (to delete the invalid data).

Information: Much like the Warning alert style, the Information alert style doesn't do anything to keep the user from entering invalid data and only informs them. This alert style offers two choices to the user: OK (to enter the invalid data) and Cancel (to delete the invalid data).

There are three types of error alerts.

You can also entirely disable the alert by unchecking Show error alert after invalid data is entered option. Doing this, however, undermines the whole point of data validation, since the Data Validation rule will exist but won't enforce in any way.

Data Validation Example: Creating a Drop-Down List in Excel

Now to put together everything you've learned, let's use Data Validation to create a drop-down list.

Drop-down lists are an excellent way for forcing the users to select an item from a list you provide them with, rather than entering their own values.

This increases the data entry speed and also reduces the chances of user-made mistakes in data entry. You can create drop-down lists with Data Validation.

Drop-down lists are different from custom lists. You can use custom lists in Excel to store data you use often. If you want to learn more about custom lists, read our article on how to create a custom list in Excel.

For this example, let's say we have a list of student names, and we're going to choose their courses from a drop-down list. First things first, let's create a table of names:

  1. Select cell A1 and type "NAME".
  2. In the cells below, add a bunch of names.
  3. Hover your cursor under the column header (i.e. A) and click it when the cursor becomes an arrow. This will select the entire column.
    Click the column header to select the entire column.
  4. Go to the Insert tab and then click on Table.
  5. Check My Table Has Headers and then click OK. This option will list the top cell (i.e. NAME) as the table header.

Next up, let's create a table for courses where the user will be able to select a course for each student.

  1. Select cell B1 and type "COURSE".
  2. Select the entire column by clicking right under the column name (same as the previous table).
  3. Go to the Insert tab and then select Table to create a table.
  4. Check My Table Has Headers and then click OK.
You can also give some color to your tables.

Now that you have the table for courses, it's time to provide a list of courses.

  1. Click the plus sign next to Sheet1 to create a new sheet.
  2. In the new sheet, type the name of the courses in a column.

Related: How to Work With Worksheet Tabs in Excel

Finally, let's use Data Validation to create a drop-down list of the courses.

  1. Select the cells in the COURSE column.
  2. Go to the Data tab and then click on Data Validation.
  3. In the Settings tab, under Allow, choose List.
  4. Click the Source box.
  5. Go to Sheet2 and select the cells containing the course names (A1:A5 in this example).

So far, you've created the Data Validation rule. If you want to do this thoroughly, you should add an input message as well.

  1. In the Data Validation window, Go to the Input Message tab.
  2. Type in a title and then a custom message.
Create an input message to inform the user.

Finally, let's enforce this Data Validation rule. Even though we've provided a list for the users to choose data from, it's still possible to type other data into the cell. To prohibit this, you should create an Error Alert.

  1. In the Data Validation window, go to the Error Alert tab.
  2. Set the Style to Stop. By doing this, the user will only be able to enter what's in the list.
  3. Type in a Title and then enter an Error Message. You can also leave these blank.
  4. When you're all set, click OK in the Data Validation window.
Create an error alert to enforce the law.

That's it! Go ahead and check out your results. Click the cell in front of each name and watch the drop-down list you just created. Or try entering invalid data to get hit with the Error Alert.

You've created a neat list with Data Validation in Excel.
The Error Alert functions properly.

There are various approaches to creating a drop-down list. For a more in-depth guide, read our article on dropdown lists in Excel.

Control The Data In Your Excel Document

Now that you have learned the basics of Data Validation, you can maximize accuracy in data input from your users or simplify data entry with a drop-down list. You've learned to utilize a useful Excel feature, but there's still much more to learn.