How to Create Multiple Linked Drop Down Lists in Excel

This guide shows you how to create multiple drop-down lists in Excel where the choices in one list depend on the selection made in another. You’ll end up with a cascading or dependent list system—for example, choosing a category like “Fruits” in the first list, and then seeing only fruits (Apple, Banana, Cherry) in the second list. This is a powerful way to keep data entry consistent and error-free, especially in dashboards, data entry forms, or inventory sheets.

What You’ll Need

• Microsoft Excel (2010 or later works; the steps are similar across versions, though the menu names may differ slightly).
• A list of your main categories (e.g., “Fruits”, “Vegetables”).
• For each category, a separate list of its items (e.g., “Apple, Banana, Cherry” for Fruits).

Step-by-Step

1. Prepare your data lists. In a separate area of your sheet (or on a hidden sheet), type your main categories in one column. For each category, type its items in a column that is directly to the right of the category name. For example:
   • Column A: Fruits, Vegetables
   • Column B: Apple, Banana, Cherry (next to “Fruits”)
   • Column C: Carrot, Lettuce, Spinach (next to “Vegetables”)
2. Name each list. Select the cells containing your main categories (e.g., A1:A2) and type a name in the Name Box (the box to the left of the formula bar), like Categories, then press Enter. Do the same for each item list: select B1:B3 and name it Fruits, select C1:C3 and name it Vegetables. Names cannot have spaces.
3. Create the first (main) drop-down list. Select the cell where you want the first drop-down (e.g., D2). Go to the Data tab, click Data Validation (or Data > Validation on Mac). In the dialog, under Allow, choose List. In the Source box, type =Categories (or select the range directly). Click OK.
4. Create the second (dependent) drop-down list. Select the cell for the second drop-down (e.g., E2). Open Data Validation again. Under Allow, choose List. In the Source box, enter the following formula: =INDIRECT(D2). This tells Excel to use the value in D2 as a range name, so if D2 says “Fruits”, it looks for a named range called “Fruits”. Click OK.
5. Test it. Click on the first drop-down and pick a category. Then click on the second drop-down—it should now show only the items from that category. If you change the first list, the second list updates automatically.
6. Copy the lists down (optional). If you need multiple rows of dependent lists, select both cells (D2 and E2) and drag the fill handle down. The named ranges will work for each row, as long as the first cell in each row contains a valid category name.

Common Mistakes

• Not naming the ranges exactly as typed. The INDIRECT formula is case-insensitive, but any extra space or typo in the name will break it. Make sure the name in the Name Box matches the category text exactly.
• Using spaces in range names. Excel does not allow spaces; use underscores instead (e.g., Fruits_List). If you must have a space, you cannot use INDIRECT directly—you’d need a different approach.
• Forgetting to use the equals sign in the Source. If you type just Categories without the =, Excel treats it as a literal text list, not a reference.
• Putting the item lists in rows instead of columns. The INDIRECT method works best when each item list is in its own column. If your data is in rows, you’ll need to use a different formula or transpose the data.
• Leaving blank cells in your lists. If your named range includes blank cells, the drop-down will show blanks. Keep the ranges tight.

Tips

• Use a hidden sheet for your lists. This keeps your data clean and prevents users from accidentally editing the source lists. Just right-click a sheet tab, choose Hide, and the lists are still accessible.
• Make your lists dynamic. If you expect to add items later, use Excel Tables (Insert > Table) for your source lists. Then name the table columns, and the drop-down will automatically include new items.
• Add error alerts. In Data Validation, go to the Error Alert tab and type a message like “Please select a valid item.” This prevents users from typing anything that isn’t in the list.
• Use the OFFSET formula for more complex dependencies. If you have multiple levels (e.g., Category > Subcategory > Item), you can use named ranges with OFFSET, but INDIRECT is simpler for two levels.

Troubleshooting

• Second list shows “Source currently evaluates to an error”. This usually means the first cell is empty or contains a value that is not a named range. Make sure you’ve selected a category in the first list, and that the named range exists exactly as typed.
• Drop-downs are not updating when I change the first list. This can happen if you copied the cells with the fill handle but the formula didn’t copy correctly. Double-check that the second cell’s Data Validation source is =INDIRECT(D2) (relative to its row).
• Named ranges are not appearing in the Name Box. Go to Formulas > Name Manager to see all named ranges. If they are missing, re-create them.
• I get a message “The list source must be a delimited list, or a reference to a single row or column.” This happens when you try to use a multi-column range. Your named range for the main list must be a single column, and each item list must be a single column.
• The INDIRECT formula works but shows a blank list. Check that the named range actually contains data. If you used a Table, the name might be the table name, not the column name—adjust accordingly.

Frequently Asked Questions

Can I have more than two linked lists?

Yes, you can cascade further. Create a third list that uses INDIRECT to reference the second list’s cell, and so on. Each level needs its own set of named ranges.

Do the lists have to be on the same sheet?

No, they can be on any sheet, including a hidden one. Just make sure the named ranges are workbook-level (by default, they are).

What if I want the lists to update automatically when I add new items?

Use Excel Tables for your source lists. When you add a row to the table, the named range (table column) expands automatically, and the drop-downs will include the new item.

Can I use this method in Google Sheets?

Yes, the same concept works in Google Sheets, but the syntax is slightly different. Instead of INDIRECT, you can use a custom formula in Data Validation, but the simplest is to use named ranges and INDIRECT as well.

Why is my INDIRECT formula not working with numbers?

INDIRECT expects a text string that looks like a range name. If your category is a number (like 1, 2, 3), you’ll need to name the ranges with numbers (e.g., “1”, “2”), but Excel may not allow pure numbers as names. Use a prefix like “Cat_1”.

Is there a way to remove the drop-down arrow for a cleaner look?

Yes, in Data Validation, uncheck the “In-cell dropdown” option. However, this also removes the arrow, so users won’t know it’s a drop-down unless you add a hint or use conditional formatting.

Related

• WhatsApp Stickers: How to Use, Make & Send Them
• Fix DISM Error 0x800700: Find the Full Code First
• Transfer SIM or eSIM from iPhone to iPhone: Step-by-Step