Excel Data Validation Drop-Down List (5 Practical Examples)

In Microsoft Excel, a drop-down list is one of those tools that allows you to validate your data in the worksheet. It helps you save a lot of time selecting a particular range of values. If your cell only takes specific values, you don’t have to type again and again. Instead, you can create a drop-down list for data validation in your Excel worksheet. In this tutorial, you will learn exactly how to create an Excel data validation drop-down list.

Our dataset contains the sales statements of a company. Using the dataset, we will create a drop-down list in Excel. Here is the overview of our dataset.

What Is Data Validation in Excel?

Now, data validation allows you to control your input in a cell. When you have limited values to enter a field, you can use the drop-down lists to validate your data. You don’t have to enter data by typing again and again. The data validation list also ensures that your inputs are error-free.

Now, why is it called data validation? Because it makes sure only the valid data makes the list. It is helpful for those users who are introduced to the dataset. They don’t have to manually input the data. Instead, they can choose any values from the drop-down list that you’ve created.

Excel Data Validation Drop-Down List: 5 Practical Examples

In the following sections, you will learn to create an Excel drop-down list for data validation in various ways. I recommend you learn and apply all these methods in your dataset. I hope it will develop your Excel knowledge. Let’s get into it.

1. Create Drop-Down List in Single Cell from Comma Separated Values

To create a simple drop-down list you have to provide some values from which users can choose. You can give those values in various forms. One of them is using the Comma Separated Values. I will create a data validation drop-down in a single cell from comma-separated values here.

• Select cell B5 >> Go to Data tab >> from the Data Tools group select Data Validation feature >> Data Validation.

• As a result, a Data Validation dialog box pops up. Now, select the Setting tab >> Choose List from the Allow drop-down list >> Input the valid data in the Source typing box that is acceptable to the cell, and I gave some sample data using commas >> Hit OK.

• As you can see, the list we created is shown here. Now, click any data that you want to enter in the cell. In this way, you can create an Excel data validation using the drop-down list.

2. Make Drop-Down List in Multiple Cells

Now, we have created a drop-down list for a single cell. But, what if we want to do that for multiple cells? It is pretty simple.

• Create a data validation drop-down list like the previous example. Hence, drag the AutoFill feature from cell B5 to cell B13 to make a drop-down list in multiple cells.

• You can create a drop-down list another way for data validation for multiple cells. Select all the cells that you want to validate, we select cells B5 to B13 from our dataset. Hence, do like the below screenshot.

• Finally, you will be able to create a data validation drop-down list in multiple cells.

• To easily understand the example, please refer to the video file below.

3. Create Data Validation Drop-Down List from Range of Values/ Named Range

In this example, we will create a drop-down list from a range of values and use the Named range. We have a dataset that contains information about several products. The Product ID, Product Name, and Amount of Sales are given in columns B, C, and D. Using this dataset, we will create a drop-down list.

3.1 From Range of Values

We will learn how to create an Excel data validation drop-down list from a range of values.

• Select cell F5 >> Choose Data tab >> from the Data Tools group select the Data Validation.

• A Data Validation dialog box pops up. After that, select the Setting tab >> Choose List from the Allow drop-down list >> Type $C$5:$C$13 in the Source typing box >> Press OK.

• Finally, you can create a data validation drop-down list in Excel using a range of values.

Notes:

If additional data is added to the table after the table itself, the drop-down list will not be updated to include the new data. However, if a cell is inserted within the source data table, any data in that cell will be included in the drop-down list. Have a look at the video below.

3.2 Using Named Range

Now we are using the Named Range to make a drop-down list in Excel. Let’s follow the instructions below to learn!

• Select cells C5 to C13 and give the name of this range as Product.

• After that, repeat the above example, and type the following formula in the Source typing box of the Data Validation dialog box.

• The drop-down list will be created using the Named range like the following screenshot.

Read More: How to Make a Data Validation List from Table in Excel

4. Use List to Make Data Validation Drop-Down List

Previously, we created a drop-down list where our range of values was in the same sheet. Now, you can also choose the values in the Source field from another sheet or Workbook to create a data validation drop-down list.

4.1 From Another Sheet

In this section, we will create a drop-down list using the Source field from another sheet.

• You can see from the screenshot, we have used a list from a different sheet named List. And in the Source field, you can see the sheet name and the cell references.

• The final output is given in the below screenshot.

Read More: How to Use Data Validation List from Another Sheet

4.2 From Another Workbook

Now, we will make a drop-down list using the source data from another workbook. To do that, type the following formula in the Source field under the Data Validation dialog box, and then press OK.

Now, you can be able to make a drop-down list using source data from another workbook.

Notes:

The workbook which contains the source data must be open in order for the drop-down list to work. If the other workbook is closed, the drop-down list will display an error message

5. Make a Searchable / Dynamic / Dependent or Conditional Drop-Down List in Excel

Here we will learn how to make a Searchable/Dynamic/Dependent or Conditional drop-down list in Excel. Let’s follow the instructions below to learn!

5.1 Searchable Drop-Down List

We will create a searchable dropdown list using Excel formulas.

• Insert the following formula in cell E5 in the sheet named SalesInfo.

Then select cell B4 in the Searchable worksheet. Next, select Data >> Data Validation. Now choose List from the Allow: field >> Enter the following formula in the Source field.

• Go to the Error Alert tab >> Uncheck Show error alert after invalid data is entered >> Press OK.

• Finally, a searchable drop-down list has been created. Now type something (F) in cell B4. Then select the dropdown arrow visible at the lower right corner of the cell.
• After that, you will see all the relevant search results as shown in the following picture.

Read More: Excel Data Validation Drop Down List with Filter

5.2 Dynamic Drop Down List

The dynamic drop-down list will allow you to auto-update your data. Follow these steps to learn.

• We will use the same datasheet here. Now in the source field, apply the following formula. Hence, press the OK.

Finally, you will be able to create a dynamic drop-down list.

Now let’s check whether this list is linked with a cell value or not. Delete some data from your data list and see that the drop-down list automatically updates itself. So the formula is working correctly.

5.3 Dependent or Conditional Drop Down List

The dependent or conditional drop-down list is also a formula-based list and dynamic. Consider this example where the Fruit and Vegetables columns are given with some data. We need to make a dependent list from that. Let’s follow the instructions below to learn!

• Now create a drop-down list from the column’s name.
• Click “OK” to continue.

• We have our drop-down list for the columns.

• To complete the task, select the “Fruit” and “Vegetable” column, go to “Formulas” and in the “Name Manager”, click on “Create From Selection”.

Formulas → Defined Names → Create from Selection

• A new window popped out. Check on the Top Row and click OK.
• Select cell F4 and go to “Data Validation” and select “List”. In the “Source” box, apply this formula,

• This means that when you select “Fruit” in the drop-down list (E4), this refers to the named range “Fruit” (through the INDIRECT function) and thus lists all the items in that category.
• Click “OK”. The formula-based dependent list is made.

• Now if you change “Fruits” from “Vegetables”, the list will show you the vegetable name.

How to Handle Errors in Data Validation Drop Down List

While inserting any item that is not on our list, Excel shows an error. Let’s enter an item that is not on our list. Let’s say, we will insert Motor and press Enter. You will see the following message:

As our item was not on the list, it won’t take this as a valid item. This is an Error Alert in data validation. You can customize it in various ways.

In Microsoft Excel, you can show three types of error messages. These are Stop, Warning, and Information.

Select the Title and Error Message you want to show when a user gives an invalid input. At last, press OK.

1. Stop Style Error

It will appear when the user gives an invalid entry. This option allows the user to retype or cancel the attempt.

2. Warning Style Error

The warning style shows a message that gives a user a choice to allow the item that is not in the list you selected.

3. Information Style Error

The Information style shows a message that automatically authorizes the item no matter what the user gave. It shows the user the data validation rules.

How to Allow Entries That Are Not in Excel Drop-Down List

When you add the data validation, an error alert is automatically turned on. That means you can not enter invalid items in the column. Now, you may be in a situation where you have to allow the user to enter items that are not selected in the list. In this situation you can follow two methods:

1. Turn Off Error Checking

To allow entries that are not on the list, you can turn off the error-checking option. By doing that, Excel won’t show any error message for other values and it will accept any item given by the user.

In the Data Validation dialog box, select the Error Alert tab. Then uncheck the option as we showed in the picture.

After that, you can enter any other values outside the list in the Excel data validation list.

2. Choose Other Error Alerts Options

Another useful way to allow other entries is to choose different error alert options. We have already shown you different types of error alerts. According to me, choose the Information style.

This error alert allows you to enter different items in the column.

How to Remove Drop-Down List in Excel

In this section, we will learn how to remove a drop-down list in Excel. To do that,

select the drop-down cell >> Choose Data tab >> Select Data Validation feature >> From the Data Validation dialog box select Clear All option >> Hit OK.

Finally, you will be able to remove the drop-down list.

Benefits of Using Data Validation Drop-Down List

The Data Validation Drop-Down List feature in Microsoft Excel offers several benefits. Here are some of the key benefits:

1. Reduces errors: By restricting the entries in a cell to a predefined set of values, the Data Validation Drop-Down List reduces the chances of errors in data entry.

2. Saves time: The drop-down list makes data entry faster and more efficient, as users can select a value from the list instead of typing it manually.

3. Consistency: The Data Validation Drop-down List ensures that the data entered in a cell is consistent and conforms to the predefined set of values.

4. Flexibility: You can easily modify the list of values in the drop-down list as needed, without affecting the existing data

Things to Remember

• You can copy any cell with data validation and paste it into other cells. The resulting cells will have the same drop-down list.
• Use a separate sheet to store the list of values that you want to appear in the drop-down list. This makes it easier to manage and update the list of values.
• Use named ranges to define the list of values. This makes it easier to reference the list of values in other parts of the workbook.
• Consider using data validation for other types of data, such as dates or times. This can help to ensure that the data entered in the cell is valid and consistent.

Frequently Asked Questions

1. How do I make a drop-down list dependent on another drop-down list?

Answer: To make a drop-down list dependent on another drop-down list, use named ranges and the INDIRECT function. Create two named ranges, one for the first list and one for the second list, and use the INDIRECT function in the Source box of the second list to reference the first list based on its value.

2. How do I prevent users from entering data that is not in the drop-down list?

Answer: To prevent users from entering data that is not in the drop-down list, go to the Data tab, click on Data Validation, select List from the Allow drop-down menu, and make sure to check the box next to In-cell dropdown and Ignore blank. This will limit user input to the values in the drop-down list.

3. Is a drop-down list equivalent to data filtering?

Answer: No, a drop-down list and data filtering are not the same. A drop-down list allows the user to select a value from a predefined list of options, while data filtering allows the user to narrow down a larger set of data based on specific criteria. Both can be used to facilitate data entry and analysis in Excel, but they serve different purposes.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to create an Excel data validation drop-down list. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Related Articles

• How to Set Limit in Excel Cell
• How to Use Excel Formula Not to Exceed a Certain Value

<< Go Back to Create Drop-Down List in Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel