Share this article:

Dropdown lists in Microsoft Excel are an excellent way to improve data entry efficiency, consistency, and accuracy. By providing a predefined list of options, you can prevent users from entering incorrect or inconsistent data. Dropdown lists are created using Excel’s Data Validation feature, which allows you to set specific rules for data entry in a cell or range of cells. In this article, we will guide you through the process of creating dropdown lists in Excel, including examples to demonstrate their use in various scenarios.

Creating a Basic Dropdown List

To create a basic dropdown list in Excel, follow these steps:

  1. Select the cell(s) where you want to create the dropdown list.
  2. Go to the ‘Data’ tab in the Excel ribbon.
  3. Click ‘Data Validation’ in the ‘Data Tools’ group.
  4. In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow’ dropdown menu.
  5. Enter the list items in the ‘Source’ box, separated by commas, or select a range of cells containing the list items.
  6. Click ‘OK’ to create the dropdown list.

Example 1: Dropdown List for Departments

Suppose you have a worksheet where you need to enter the department for each employee. To ensure consistency and accuracy, you want to create a dropdown list containing the department names. Here’s how to do it:

  1. Select the cell(s) where you want to create the dropdown list (e.g., B2:B10).
  2. Go to the ‘Data’ tab and click ‘Data Validation’.
  3. In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow’ dropdown menu.
  4. Enter the department names in the ‘Source’ box, separated by commas: Human Resources, IT, Sales, Marketing, Finance
  5. Click ‘OK’ to create the dropdown list.

Now, when you click on the selected cell(s), a dropdown arrow will appear, allowing you to choose from the predefined department names.

Example 2: Dropdown List Using a Range of Cells as Source

In some cases, you might want to use a range of cells as the source for your dropdown list. This can be particularly useful if you have a long list of options or if the list is subject to change. Here’s how to create a dropdown list using a range of cells as the source:

  1. First, create a list of options in a separate range of cells (e.g., in column F, rows 2 to 6):FRedGreenBlueYellowOrange
  2. Select the cell(s) where you want to create the dropdown list (e.g., A2:A10).
  3. Go to the ‘Data’ tab and click ‘Data Validation’.
  4. In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow’ dropdown menu.
  5. Click the ‘Source’ box and select the range of cells containing the list items (F2:F6).
  6. Click ‘OK’ to create the dropdown list.

Now, when you click on the selected cell(s), a dropdown arrow will appear, allowing you to choose from the predefined color options in the range F2:F6.

Adding and Removing Items from the Dropdown List

If you used a range of cells as the source for your dropdown list, you can easily add or remove items by simply updating the list in the source range. When you update the source range, the dropdown list will automatically update to reflect the changes.

Using Dropdown Lists with VLOOKUP

Dropdown lists can be used in conjunction with the VLOOKUP function to create dynamic, interactive worksheets. By using a dropdown list as the lookup value in a VLOOKUP formula, you can quickly retrieve information from a large dataset based on the selected option.

Example 3: Dropdown List with VLOOKUP for Product Price Lookup

Suppose you have a product list with product names, categories, and prices. You want to create a dropdown list to select a product and display its price using the VLOOKUP function.

Sample Data:

ABC
Product NameCategoryPrice
LaptopElectronics1200
RefrigeratorHome Appliances800
HeadphonesElectronics150
  1. First, create a list of product names in a separate range of cells (e.g., in column E, rows 2 to 4):
E
Laptop
Refrigerator
Headphones
  1. Select a cell where you want to create the dropdown list (e.g., G2).
  2. Go to the ‘Data’ tab and click ‘Data Validation’.
  3. In the ‘Data Validation’ dialog box, select ‘List’ from the ‘Allow’ dropdown menu.
  4. Click the ‘Source’ box and select the range of cells containing the product names (E2:E4).
  5. Click ‘OK’ to create the dropdown list.

Now, when you click on cell G2, a dropdown arrow will appear, allowing you to choose from the predefined product names.

  1. In another cell (e.g., H2), enter the following VLOOKUP formula:=VLOOKUP(G2, A1:C4, 3, FALSE)

Explanation: This formula searches for the value in cell G2 (the selected product) in the first column of the table (A1:C4). When it locates the value, it retrieves the data from the 3rd column in the same row and returns the price.

Now, when you select a product from the dropdown list in cell G2, the price will automatically display in cell H2.

Conclusion

Dropdown lists in Microsoft Excel are a powerful tool for enhancing data entry efficiency, consistency, and accuracy. By creating dropdown lists using the Data Validation feature, you can provide users with a predefined set of options and prevent incorrect or inconsistent data entry. The examples in this article demonstrate the versatility of dropdown lists and how they can be used in various scenarios, including using them with VLOOKUP functions to create dynamic, interactive worksheets. By mastering the process of creating dropdown lists, you can significantly improve the functionality and usability of your Excel workbooks.