Google Sheets Dropdowns From Named Ranges: How to Set Up

Yes. In Google Sheets, create a named range for your list, then use that name as the source for a data-validation dropdown: select the destination cells, choose Data > Data validation > Add rule, select Dropdown (from a range), and enter =CategoryList using your own named range. This makes the dropdown easier to maintain than repeatedly typing a cell address, especially when several cells or sheets use the same choices.

What a named range does

A named range gives a cell or group of cells a readable label. Instead of referring to a list as Lists!A2:A12, you can name that range CategoryList. The label can then be used in places that accept a range reference, including a dropdown’s source field. The list itself stays in ordinary spreadsheet cells; naming it does not copy the values or turn them into a separate object.

This is useful when a workbook has repeated lists, such as departments, product types, project statuses, or regions. If several dropdowns draw from the same named range, you can update the source list once rather than hunt through multiple validation rules. A named range also makes a rule easier to understand later: a source called ProjectStatuses is more informative than a bare cell address.

Prepare and name the source list

Put the choices in one column or row, preferably on a separate sheet used for lookup data. Keep the list together and use one choice per cell. For example, a sheet named Lists could contain these entries in A2:A5: Planned, In progress, Blocked, and Complete. A heading in A1 is fine, but exclude it from the named range so it does not become a dropdown choice.

To name the cells in Google Sheets, select the values, then choose Data > Named ranges. In the side panel, enter a short name such as ProjectStatuses, check that the displayed range includes the intended cells only, and click Done. Choose a descriptive name without spaces; underscores can help separate words. Names also cannot be written like a cell address, such as A1.

Before continuing, check for accidental empty cells, duplicate choices, or inconsistent spelling. A source list containing both In progress and In Progress can produce two options that look nearly identical. If you expect the list to change, leave a clear place to add entries and remember that the named range covers only the cells it was assigned.

Create the dropdown from the named range

Select the cell or range of cells where people should make a choice. For example, select B2:B100 on a Tasks sheet.
Choose Data > Data validation, then click Add rule if the sidebar does not already show a new rule.
In Apply to range, confirm that the intended destination cells are listed. In Criteria, choose Dropdown (from a range).
In the source range field, enter the named range with an equals sign, such as =ProjectStatuses. Use the exact spelling of the name you created.
Choose how invalid entries should be handled. Reject input prevents a value outside the list from being entered; Show a warning allows it but flags it. Click Done.

Click a cell covered by the rule to check the result. The dropdown should offer Planned, In progress, Blocked, and Complete. Selecting an option writes that text into the cell; the named range does not create a formula in the destination cell. If the range field will not accept the name, verify the name under Data > Named ranges and try the direct source address, such as Lists!A2:A5, to distinguish a naming issue from a validation issue.

A practical example with shared choices

Suppose a team tracks requests on a sheet called Requests and keeps their approved priority choices on a Lists sheet. Put Low, Normal, High, and Urgent in Lists!B2:B5, name those cells PriorityChoices, and apply a dropdown sourced from =PriorityChoices to Requests!C2:C200. Every request row can then use the same four approved labels.

If the team later decides to add Critical, put it in B6 and expand the named range so it includes B2:B6. The existing validation rules that refer to the named range can then use the updated source. Merely typing Critical below the old range does not necessarily add it: a named range refers to a particular address, and a fixed range such as B2:B5 does not automatically include B6. This is one of the most common causes of a newly added choice missing from a dropdown.

For repeated rules, you can apply the same named-range source to several destination cells at once by selecting a destination range before creating the rule. You can also edit an existing rule through Data > Data validation. Check Apply to range before changing anything; changing a rule that covers many cells can affect all of them.

Keeping the list current

A named range makes the source easier to reuse, but it does not by itself make the source automatically growing. If you have named A2:A10, entries typed in A11 are outside the range until you edit the named range. To add choices, open Data > Named ranges, select the name, and update its range to include the new cells. Then verify the dropdown again.

One approach is to reserve extra rows in the named range, for example A2:A50, so ordinary additions fit without editing the range. The trade-off is that unused cells may be part of the source and can make the dropdown behave less neatly, depending on the validation interface and whether the cells contain blanks. Another approach is to expand the range only when the list changes. For a small controlled list, that is often clearer than maintaining a large, mostly empty source area.

Keep the source list in a place collaborators can find, and avoid sorting or deleting its cells without checking the named range afterward. Inserting or removing rows can change the referenced range. A quick check in Data > Named ranges confirms which cells the name currently points to.

Common problems and fixes

The source is rejected: Check that the named range exists, is spelled correctly, and is entered as =NameOfRange in the dropdown source field. If necessary, choose the source cells directly with the range selector and confirm that the resulting address points to the right sheet.
The new option is missing: The named range probably stops above the new value. Expand its cell range under Data > Named ranges, then reopen the dropdown.
The heading appears as an option: Edit the named range so it starts at the first actual choice, not the column heading.
Some cells have no dropdown: Inspect Apply to range in the rule. The rule may cover only part of the intended column or may have been replaced by a different rule.
A user can type an unlisted value: Edit the rule and choose Reject input if entries must match the list. Show a warning is less restrictive and still allows other values.
Choices appear duplicated: Review the source for repeated values, extra spaces, or differences in capitalization. Clean up the source cells and make sure the range contains only the intended list.

Also check for overlapping validation rules when a cell behaves differently from nearby cells. Selecting the affected cell and opening Data > Data validation lets you see the rules applied to it. If you change the source range but an old rule remains, edit or remove the outdated rule rather than assuming the named range update replaced every validation setup.

Named ranges compared with direct cell references

A direct range such as Lists!A2:A5 is perfectly suitable for a one-off dropdown. It is explicit and easy to verify when the source is short and unlikely to be reused. A named range is more readable and convenient when a list supports multiple dropdowns or when the source is maintained by someone other than the person who created each rule.

Neither approach automatically solves list maintenance. If the source grows beyond its current cells, update the referenced range or named range. A named range is not a special dynamic list, and it does not automatically remove duplicates, sort entries, or filter out blanks. Those are separate list-design choices. For a simple workbook, a carefully maintained column and a named range are usually easier for collaborators to understand than a more elaborate setup.

Sharing, permissions, and Excel differences

In a shared Google Sheets file, the dropdown is part of the sheet’s validation rule, while the choices remain in the source cells. Someone with sufficient editing access may be able to change the rule or the source list. If the choices are meant to be controlled, protect the source sheet or range using Data > Protect sheets and ranges, and set permissions appropriately. Protection helps prevent accidental edits; it should not be treated as a substitute for carefully managing who can edit the spreadsheet.

Google Sheets and Excel have similar concepts but different interfaces and reference behavior. These steps and menu names are for Google Sheets in a web browser. In Excel, named ranges and list validation are managed through Excel’s own Data Validation controls, so do not assume that the Google Sheets sidebar or its exact source syntax applies there. If a workbook is converted or imported between the two products, check the validation rules and named ranges in the destination file.

Frequently Asked Questions

Can I use a named range for a Google Sheets dropdown?
Yes. Create the named range for the source cells, then select Dropdown (from a range) in the validation rule and enter the name as a range reference, for example =DepartmentList.

Do I type the named range with an equals sign?
For the dropdown’s range source field, use the equals-sign form, such as =DepartmentList. If Sheets reports an invalid range, confirm the name in Data > Named ranges; using the source cells’ direct address is a useful fallback.

Will the dropdown update when I add a new choice?
Only if the added cell is inside the named range. If the name refers to A2:A10 and you add a value in A11, expand the named range to include A11 before expecting it to appear.

Can one named range feed dropdowns on multiple sheets?
Yes. Apply a validation rule on each destination sheet and use the same named range as the source. The range can be reused; each destination still needs an appropriate validation rule.

Can I stop people from typing values outside the list?
Yes. In the data-validation rule, set invalid data to Reject input. If you choose Show a warning instead, a person can still enter a value that is not in the dropdown 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.