How to Create a Drop Down List in Excel Using Formulas?

Sharing is caring!

Do you want to create a drop down list in Excel to make data entry faster and more accurate? Using a drop down list allows you to select from a predefined list of options rather than manually typing in values. This is especially helpful when you have a long list of possible entries or want to ensure consistency in the data being entered.

In this article, we’ll show you step-by-step how to create a drop down list in Excel using formulas. We’ll cover two methods:

1. Using the Data Validation feature
2. Using named ranges and the INDIRECT function

What is a Drop Down List in Excel?

A drop down list (also known as a drop down menu) is a graphical control element that allows the user to select one value from a predefined list of options. When you click on a cell containing a drop down list, it displays a small arrow that can be clicked to expand and view the full list of available choices. The selected value from the list is then automatically entered into the cell.

Using drop down lists in Excel provides several benefits:

• Speeds up data entry – Users can quickly select from the list rather than typing
• Ensures data accuracy and consistency – Prevents typos and entries not on the predefined list
• Makes spreadsheets more user-friendly, especially for other people entering data into your worksheet
• Saves time on data validation since invalid entries are avoided in the first place

Drop down lists are ideal anytime you have a cell or column in your spreadsheet that will contain one of a finite set of possible values. Some common uses include listing out states, countries, product names, categories, or payment types.

Method 1: Create a Drop Down List Using Data Validation

The easiest way to create a simple drop down list in Excel is by using the built-in Data Validation feature. Data Validation allows you to define restrictions on what data or type of data can be entered into selected cells.

Step 1: Create Your List of Values

First, you need to create the list of values that will appear as choices in your drop down menu.

1. Select an empty cell where you want the list of values to appear in your workbook. It can be on the same worksheet as your data or on a separate sheet.
2. Type in the values you want to appear in your list, entering each one in its own cell in a single column.
3. Select the entire range of cells containing your list values.
4. Go to the Formulas tab on the Excel ribbon and select Define Name from the Defined Names group.
5. In the New Name dialog box that appears, enter a name for your list (e.g. “ProductList”, “Countries”, etc.).
6. Click OK to finish creating the named range.

Your list of values is now defined as a named range and ready to be used in your drop down list. Using a named range makes your formulas easier to understand and maintain.

Step 2: Apply Data Validation to Create the Drop Down List

With your named list created, you can now use the Data Validation feature to associate that list with the cells where you want the drop down menu to appear.

1. Select the cell or range of cells where you want to have the drop down list. This is where users will be selecting from your predefined options.
2. Go to the Data tab on the Excel ribbon and click Data Validation in the Data Tools group.
3. In the Data Validation dialog box that appears, go to the Settings tab.
4. In the Allow box, select List to indicate you want to restrict entries to a list of values.
5. Click in the Source box and enter =NameOfYourList (replace NameOfYourList with the actual name you defined for your range in Step 1).
6. Click OK to close the dialog box and apply the data validation.

Your drop down list should now appear in the selected cell(s). When a user clicks on the cell, they’ll see a small arrow they can use to expand the list and select a value. Here’s an example of what the filled out Data Validation dialog box looks like:

If you want to change the default behavior of the drop down list, you can adjust the other settings on the Data Validation dialog box. Some options include:

• Ignore blank – Allow the cell to be left empty in addition to the list values
• In-cell dropdown – Show the drop down arrow only when the cell is selected
• Input Message – Display a message when the cell is clicked, such as instructions
• Error Alert – Show a warning message if a user tries to enter a value not in the list

Method 2: Create a Drop Down List Using INDIRECT Function

For more advanced users, another way to create a drop down list is by using the INDIRECT function in combination with named ranges. This method allows you to create more flexible and dynamic drop down lists that can change based on the value in another cell.

Step 1: Define Named Ranges for Your Lists

Start by setting up all your lists of values as named ranges, similar to the first method:

1. For each set of drop down options, select the range of cells containing those values.
2. Go to the Formulas tab and click Define Name.
3. Enter a meaningful name for the range and click OK.
4. Repeat this process to create a named range for each distinct list you’ll be using.

For example, let’s say we want to create two drop downs on our sheet – one for selecting a salesperson and one for selecting a region. Our salespeople are listed in cells A2:A10 and our regions are listed in cells B2:B5. We’ll define two named ranges called “SalespersonList” and “RegionList” respectively.

Step 2: Use the INDIRECT Function to Create the Drop Downs

Now that we have our lists defined, we can use the INDIRECT function to actually create the drop down menus.

1. Select the cell where you want the first drop down list to appear.
2. Go to the Data tab and click Data Validation.
3. In the Data Validation dialog box, choose List from the Allow dropdown.
4. In the Source box, enter a formula like this:
   =INDIRECT(“NameOfList”)
5. Replace NameOfList with the actual name you defined for the list in Step 1. In our example, we’d enter “SalespersonList”.
6. Click OK to create the drop down list.
7. Repeat steps 1-6 for any other drop down lists you want to create. In our example, we’d make a second list with the source =INDIRECT(“RegionList”).

The INDIRECT function takes a text string and returns the values in the referenced named range, creating the drop down menu. One of the key benefits of this approach is that if you change the named range, it automatically updates any drop downs that reference that range.

Step 3: Make the Drop Downs Dynamic (Optional)

If you want to take it a step further and allow your users to control which drop down list appears based on another selection, you can expand the INDIRECT method as follows:

1. Create another named range (we’ll call it “Lists”) with the names of your original ranges each in their own cell, like:
   SalespersonList
   RegionList
2. Select the cell where you want the first dependent drop down list to appear.
3. Set up a data validation list in that cell with the following formula in the Source box:
   =INDIRECT(Lists)
4. Repeat step 3 for any other dependent drop downs you’re creating.

Now your users will have a drop down to select which named range to pull values from. Whatever option they select from the “Lists” drop down will change the values that appear in the dependent drop downs. This is a great way to create interactive spreadsheets that change based on user input.

How to Edit or Remove a Drop Down List

If you need to make changes to an existing drop down list, follow these steps:

1. Select the cell or range containing the drop down list you want to modify.
2. Go to the Data tab and click Data Validation.
3. In the Data Validation dialog box, make any desired changes like editing the source range, changing the error message, etc.
4. If you want to remove the drop down list completely, select Any value from the Allow box.
5. Click OK to apply your changes.

Alternatively, if you just need to quickly remove a drop down, you can simply delete the values in the source range. Either clear out the original list, or if you used a named range, go to Name Manager and delete the named range. Just be careful that you aren’t breaking any other formulas by deleting a named range.

Final Thoughts

As you can see, creating drop down lists in Excel is a straightforward process that can make a big difference in the efficiency and user-friendliness of your spreadsheets. Whether you choose the simple Data Validation method or the more advanced INDIRECT function method, you now have the tools to start implementing drop down selection in your own workbooks.

Some key things to remember:

• Use the Data Validation feature for basic drop down lists with a fixed set of values.
• The INDIRECT function allows you to create dynamic drop downs that change based on another cell.
• Defining your lists as named ranges makes them easier to reference and update.
• Don’t forget to use the additional data validation settings to control things like error messages and blank values.

With a little practice, you’ll be building powerful, interactive Excel sheets in no time. Leveraging features like drop down lists is a great way to take your spreadsheets to the next level.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.