Excel Named Range Data Validation: Create Dropdowns

How-to basics · Updated November 21, 2026

To use a named range for Excel data validation, name the cells containing your choices, then set the validation rule to a list whose Source is that name. For example, name a range of fruit choices FruitList, select the cells that need a dropdown, and choose Data > Data Validation > Allow: List; enter =FruitList in Source.

What a named range does

A named range gives a cell or range of cells a meaningful name, such as FruitList or DepartmentNames. Instead of relying on an address like Lists!$A$2:$A$8, you can refer to the choices by name. That makes a validation rule easier to read and less likely to break if the list moves.

Named ranges are especially useful when the choices are stored on a separate worksheet. Excel’s Data Validation list source is awkward for a direct reference to a range on another sheet; a defined name provides a convenient reference to that range. A name can also make a workbook easier to maintain: if the source range changes, you can update the name rather than edit every validation rule that uses it.

Create the named range

First put the choices in a single, tidy column or row. For example, enter Apple, Pear, and Orange in cells A2:A4 on a worksheet called Lists. Avoid including the column heading in the range unless you want that heading to appear as a selectable choice.

  1. Select the cells that contain the choices, such as Lists!A2:A4.
  2. Open Formulas > Define Name. In the New Name dialog, enter a short name such as FruitList.
  3. Set Scope to Workbook if the name should be available to validation rules throughout the file. Check that Refers to points to the intended cells, then select OK.

You can also select the cells and type a name into the Name Box, to the left of the formula bar, then press Enter. To inspect, edit, or remove names later, use Formulas > Name Manager. On Excel for Mac, the corresponding commands are also available from the Formulas tab; dialog placement and appearance can vary slightly.

Excel names cannot contain spaces and must follow name rules, so use names like FruitList or Fruit_List, not Fruit List. Names also cannot look like a cell address, such as A1. A workbook-scoped name is usually the simplest choice for a reusable dropdown.

Apply the data validation rule

With the name created, select the cells that should have dropdowns. To apply one rule to a column of entry cells, select the whole intended range, such as B2:B100, before opening the dialog. Then follow these steps in Excel for Microsoft 365 on Windows:

  1. Choose Data > Data Validation.
  2. On the Settings tab, set Allow to List.
  3. Check In-cell dropdown if users should see the arrow in the cell.
  4. In Source, enter =FruitList, using the actual name you created.
  5. Select OK. Click a validated cell to choose an item from the dropdown.

In this example, the list cells are Lists!A2:A4 and the dropdown cells are B2:B100. The Source entry is =FruitList; the equals sign tells Excel to use the named reference. If the name is misspelled, Excel may report that the source evaluates to an error. If the workbook has a sheet-scoped name instead of a workbook-scoped one, the rule may not resolve from another sheet, so review the name’s scope in Name Manager.

Choose a scope and organize the source list

A defined name can have workbook scope or worksheet scope. Workbook scope makes it available across sheets and is generally preferable for a list used in multiple places. A sheet-scoped name is limited to its worksheet and can be useful when the same name needs different meanings on separate sheets, but that behavior can confuse someone maintaining the workbook. If names with the same spelling exist at different scopes, a local name can take precedence on its own sheet.

Keep the source values consistent and easy to inspect. Remove accidental blank rows from the named range if you do not want blank-looking choices. The validation setting called Ignore blank concerns whether an empty entry is permitted; it does not clean up the source list or guarantee that blank cells inside the list disappear from the dropdown. Likewise, duplicate source values can appear as duplicate choices.

A clear layout also helps future edits. Store the options together on a Lists or Setup sheet, use a heading outside the named range, and avoid putting notes or unrelated data in the middle of the options. If more than one dropdown uses the same choices, reuse the same named range rather than maintaining several nearly identical lists.

Build dependent dropdowns

A dependent dropdown changes its choices according to an earlier selection. A common design has a first dropdown for a category and a second for items in that category. One traditional approach is to create a separate named range for each category, then use a formula in the second rule to refer to the range whose name matches the first selection.

For example, suppose A2 contains a category and the named ranges are Drinks and Snacks. The first dropdown offers the labels Drinks and Snacks. For a second dropdown in B2, set Allow to List and use this Source:

With the category choice in A2 and named ranges called Drinks and Snacks, use =INDIRECT($A2) as the Source for B2. The dollar sign fixes the category column while allowing the row reference to adjust when the validation rule is applied to later rows.

Names cannot contain spaces, so a category label such as Fresh Fruit will not directly match a range named Fresh_Fruit. If category labels use spaces and the corresponding names replace spaces with underscores, this Source can convert the label:

With the category text in A2 and a named range called Fresh_Fruit, use =INDIRECT(SUBSTITUTE($A2," ","_")) in the dependent list’s Source.

INDIRECT is convenient, but it depends on the text matching a valid defined name. A typo, blank category, or renamed category can make the second list fail. Changing the first selection also does not automatically erase a previously entered value in the dependent cell; users may need to choose the new item again. For a large or frequently changing category system, consider a more structured design and test the rule across several rows.

Keep dropdown choices current

A fixed named range such as Lists!$A$2:$A$4 is simple, but it will not automatically include a new choice typed in A5. You can update the name’s Refers to range in Formulas > Name Manager whenever the list grows. This manual approach is reliable for short lists that rarely change.

For a list that changes often, an Excel Table can be a useful source. Convert the choices to a table with Insert > Table, give the table and its column clear names, and define a workbook name in Name Manager whose Refers to entry points to that table column. Then use the defined name as the validation Source. Table columns expand when rows are added to the table, so the named reference can follow those additions. Test the workbook after setup, especially if the source column contains blank rows or the table is being edited by several people.

Another option is to adjust the named range whenever the source list changes. Choose the approach based on how the workbook is used: a fixed range is easiest to understand, while a table-backed name reduces routine range maintenance. Do not assume that typing below a fixed range will update it automatically; check the name in Name Manager if a new choice is missing.

Common problems and fixes

  • The dropdown is empty or gives an error: Check that the name exists, is spelled exactly the same in Source, and refers to cells containing values. Confirm that its scope is appropriate for the sheet with the validation rule.
  • New choices do not appear: The name may still refer to the original fixed address. Expand that address or use a table-backed named reference.
  • A blank choice appears: Inspect the cells included in the named range for blanks. Ignore blank is not a control for removing blank entries from the source.
  • The dropdown works on one sheet but not another: Check whether the name is worksheet-scoped. A workbook-scoped name is usually more suitable for cross-sheet use.
  • The dependent list fails for a category with spaces: Make sure the label-to-name conversion matches the actual defined name, including underscores and spelling.
  • An old dependent choice remains after the parent changes: Validation restricts future entry; it does not automatically clear a cell’s existing value. Recheck or clear that cell when changing the parent selection.

Also check the validation rule itself by selecting a cell and opening Data > Data Validation. If cells were copied from elsewhere, the copy may have replaced or altered their validation settings. Applying the intended rule again to the correct range can restore consistency.

Validation is helpful, but not a security control

Data validation helps guide normal data entry and can display an input message or an error alert. On the Error Alert tab, the Stop style is intended to prevent users from accepting an invalid value typed directly into the cell; Warning and Information alerts allow the user to proceed. A clear input message can explain what a dropdown is for or how to request another option.

Do not treat a validation dropdown as a guarantee that every cell contains an approved value. Pasting, filling, importing, or editing cells in other ways can introduce values that do not match the list or alter the rule itself. For important reporting, audit the stored values as part of the workbook process. Validation is an entry aid, not a replacement for checking data.

When to use another approach

For a very short list that will never change, typing the options directly into the Source field may be quicker. However, a typed list is harder to maintain and has a 255-character limit for the Source entry. A named range is preferable when choices are longer, reused, stored on another sheet, or expected to change.

If users need to select multiple items in one cell, standard Data Validation does not provide a multi-select dropdown. A different interface or automation would be needed, and macros introduce compatibility and security considerations. If the goal is to show a filtered list of records rather than restrict entry, a table with filters or a formula-driven report may be more suitable than validation.

Frequently Asked Questions

Can Excel data validation use a named range from another sheet?
Yes. Create a defined name that refers to the source cells, preferably with workbook scope, and use that name in the list Source, preceded by an equals sign.

Should I include the list heading in the named range?
Usually not. Include only the selectable values; otherwise the heading itself can appear as a dropdown choice.

Why does my named range not expand when I add an item?
A fixed address only covers the cells it names. Update the name’s Refers to range or use a name that refers to a table column that expands as table rows are added.

Can I use spaces in a named range?
No. Use a name without spaces, such as Fruit_List. For dependent dropdowns, make sure the formula translates the displayed category text to the matching name.

Does a dropdown remove invalid values already in the cells?
No. Data validation governs entry behavior; it does not automatically clean up existing values. Review old entries and any data pasted into the range separately.

Related

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.