How to Link a Drop-Down List in Excel

To link a drop-down list in Excel to a range, select the destination cell, choose Data > Data Validation > Data Validation, set Allow to List, and enter the source range. If you mean a second drop-down that changes based on the first, create named ranges and use an INDIRECT-based source. The steps below cover both approaches in Microsoft Excel for Microsoft 365 on Windows.

What You’ll Need

An Excel workbook and the cells where you want the drop-down list or lists to appear.
The choices you want people to select, arranged in a single column or row. Keep the source list free of blank cells between choices.
For a dependent drop-down, a parent list and a separate list of choices for each parent item. For the named-range method below, use simple labels; replace spaces with underscores in range names.

You can keep the source choices on the same worksheet or on a separate worksheet. A named range is especially useful when the choices are on another sheet, because Excel’s Data Validation source field does not accept every kind of direct cross-sheet reference.

Step-by-Step

Put the source choices in cells. For a basic example, enter the options North, South, East, and West in H2:H5. Put them in one uninterrupted range, with one choice per cell. You can place these cells off to the side of the working area or on a separate worksheet. The choices should be stored as text or numbers in cells, not typed as a comma-separated sentence into the destination cell.
Choose the cell that should contain the drop-down. Click a single cell, such as B2. To apply the same list to several cells, select the full destination range first, such as B2:B30. You can also select non-adjacent cells by holding Ctrl while clicking them on Windows. Use the Mac equivalent, Command-click, if selecting separate cells in Excel for Mac.
Open Data Validation. On the Windows ribbon, go to Data > Data Validation > Data Validation. In the dialog, open the Settings tab if it is not already selected. Set Allow to List. Keep the in-cell drop-down option selected so that Excel displays the arrow when a destination cell is active.
Enter the list source. If your source values are in H2:H5 on the same worksheet, enter =$H$2:$H$5 in Source. The dollar signs keep the source fixed if you copy the validation to other cells. Alternatively, click the Source box and select the source cells with the mouse. Do not include the header cell unless you want the header to appear as a choice.
Set how Excel handles other entries. In the Data Validation dialog, open the Error Alert tab and leave Show error alert after invalid data is entered selected if users should be limited to the listed choices. The Stop style blocks an entry that is not in the list. You can add a short title and message, such as “Choose a listed region.” Click OK to save the rule.
Test the basic list. Select B2 and click the arrow that appears. Choose one of the options, then try typing a value that is not in the list. If the error alert is set to Stop, Excel should reject that value. The chosen item is stored as the cell’s value; it does not automatically create a live link to another cell. To display the chosen value elsewhere, use a separate cell with a reference to the drop-down cell.
Prepare the parent and child lists for dependent drop-downs. Suppose a user first chooses a department and then chooses an item from that department. Put the department names in a range, for example H2:H4, and give that range the name Categories. Put each department’s choices in its own column, with a heading above each column. For example, use the heading Hot_Drinks above tea and coffee, and Cold_Drinks above water and juice. The heading text will be used to create the corresponding named range.
Create names for each child list. Select the child choices only, without their heading. Go to Formulas > Define Name. In the New Name dialog, enter a name that matches the heading, such as Hot_Drinks, check that the range reference points to the intended cells, and click OK. Repeat for each child list. Names cannot contain spaces, so use underscores in names. Keep the parent dropdown labels consistent with the names after spaces are changed to underscores; avoid punctuation or symbols in labels for this straightforward setup.
Add the parent drop-down. Select A2, open Data > Data Validation > Data Validation, set Allow to List, and enter =Categories as Source. Click OK. This links A2 to the named range containing the department names.
Add the dependent drop-down. Select B2 and open Data Validation again. Set Allow to List. With the parent choice in A2, use this Source: =INDIRECT(SUBSTITUTE($A2," ","_")). Click OK and test it by choosing a department in A2, then opening the list in B2. The formula turns spaces in the selected label into underscores, so a choice such as Hot Drinks points to the named range Hot_Drinks. If your labels already match the range names exactly and have no spaces, the simpler source is =INDIRECT($A2).
Copy the dependent rule down carefully. If you need dependent lists in more rows, copy B2 and paste it into B3:B30, or select B2:B30 before creating the validation rule. The reference $A2 fixes the parent column but allows the row number to change. That way, the list in B3 follows the choice in A3, rather than continuing to use A2. Test more than one row before sharing the workbook.

On Excel for Mac, the Data Validation command is also available from the Data tab, though the dialog layout can vary slightly by release. Excel for the web supports common list validation tasks, but the desktop app is the safer choice for setting up and diagnosing named-range dependent lists. If a menu or option is missing in the web version, open the workbook in the desktop app.

Common Mistakes

Including a heading in the source. If the source range includes its column title, that title becomes a selectable option. Select only the cells containing actual choices.
Using a relative source reference unintentionally. A source such as H2:H5 can shift when validation is copied. Use absolute references, such as $H$2:$H$5, for a fixed range.
Typing list values with the wrong separator. Excel’s regional settings affect whether a manually typed list uses commas or semicolons. Referencing cells or a named range avoids this issue and is easier to maintain.
Expecting the child choice to clear automatically. When the parent selection changes, Excel does not erase a child value selected earlier. A value left in the child cell may no longer belong to the new parent category. Clear that cell and select a valid new item.
Using inconsistent names for dependent lists. The parent text, transformed label, and named range must correspond. For example, Hot Drinks should map to Hot_Drinks if the source uses SUBSTITUTE to change spaces to underscores.
Assuming validation prevents every invalid value. Pasting data into a validated cell can bypass the usual typing alert. Data Validation helps guide entry, but it is not a security control or a guarantee that imported data is valid.

Tips

Store source options on a dedicated worksheet, such as Lists, to keep the main sheet uncluttered. Use Formulas > Define Name to make a source range easy to identify and reuse.
For a basic list that may grow, consider turning the source range into an Excel table with Ctrl+T on Windows or Command+T on Mac. Tables expand when you add rows, but Data Validation does not accept every structured table reference directly in its Source box. Use a named range or another supported reference method rather than assuming a table name will work there.
Give the destination cells a clear prompt in the Input Message tab of Data Validation. For example, tell users to choose a department before opening the item list. This reduces confusion without taking up worksheet space.
Use short, distinct choices. If two options look similar, add enough detail to distinguish them, such as “Hardware – Small” and “Hardware – Large.” This is particularly helpful when users rely on the drop-down arrow rather than entering text.
Keep a copy of the source lists visible to workbook maintainers, and document what each named range controls. You can review or edit names through Formulas > Name Manager. This makes later changes easier than searching through validation dialogs one cell at a time.
If you share the workbook, test it in the same kind of Excel environment your recipients use. A desktop workbook may behave differently in the browser when editing more advanced validation rules, even though users can often select from an existing list.

Troubleshooting

The drop-down arrow is missing. Select the cell and return to Data > Data Validation > Data Validation. Confirm that Allow is set to List and that In-cell drop-down is checked. Also check that you have selected the cell itself; Excel shows the arrow when the validated cell is active, not all the time.

Excel says the source evaluates to an error. Check that the source range exists and that the reference begins with an equals sign. For a named range, open Formulas > Name Manager and confirm its spelling and Refers to range. If the source is on another worksheet, use a named range rather than an unsupported direct sheet reference in the Source box.

The dependent list is empty or shows an error. First choose a parent item. Then confirm that its spelling maps to a defined range name. With the SUBSTITUTE version, spaces in the label must correspond to underscores in the name; other punctuation is not converted. Check that the named range contains cells and that it refers to the right worksheet. If labels contain special characters, simplify the labels or create a more deliberate naming scheme.

The child list shows the wrong category. Check the Source reference for the dependent rule. If every row points to $A$2, rows below will all use the first parent selection. Use $A2 so the column stays fixed while the row adjusts, then copy the validation rule down and test each row.

A value outside the list is still present. It may have been pasted in, or the error alert may be turned off or set to Warning or Information. Review the Error Alert tab and remove or correct existing invalid values manually. Changing the validation rule does not automatically clean up old entries.

Frequently Asked Questions

Can I link a drop-down list to a different worksheet?
Yes. Put the choices on that worksheet and define a named range for them with Formulas > Define Name. Use the name, preceded by an equals sign, as the Data Validation list source. This is generally more reliable than entering a direct reference to another sheet.

Does a drop-down list update when I change its source values?
Yes, if you edit a value in a cell already included in the source range, the option changes. If you add choices outside that range, edit the validation source or use a source setup that expands appropriately; the original fixed range will not automatically include new cells.

Can I make the second list depend on the first?
Yes. Create a named range for each set of child choices, then use an INDIRECT-based Data Validation source that converts the selected parent label to the matching range name. Keep the labels and names consistent, and remember that a changed parent selection does not clear a previous child value.

Can users type a choice instead of selecting it?
They can, depending on the error-alert settings. To restrict entries, enable the error alert and choose Stop. If you want to allow occasional values outside the list, use Warning or turn off the alert, but consider how those entries will be reviewed.

Can I link the selected value to another cell?
Yes. The drop-down cell contains the chosen value. In a separate cell, enter a normal cell reference such as =B2 to display that selection. Do not put the formula in B2 itself, because B2 is where the user needs to make the selection.

Why does changing the first selection leave an old second selection behind?
Data Validation controls which choices are offered; it does not automatically delete a value already entered in the dependent cell. Clear the old value after changing the parent, then choose a valid item from the refreshed list.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.