Google Sheets Dropdown From Range: Setup, Options & Fixes

Yes. In Google Sheets, select the cells that need a dropdown, choose Data > Data validation > Add rule, then set the criteria to Dropdown (from a range) and choose the cells containing your options. The dropdown choices then come from that source range, so you can maintain the list separately and reuse it in your sheet.

How a dropdown from a range works

A range-based dropdown connects a set of cells to a validation rule. For example, a task tracker might store status options such as Not started, In progress, and Done on a separate Lists tab. A dropdown in the tracker reads those cell values and presents them as choices whenever someone edits a validated cell.

The source range and the cells that use the dropdown can be on different tabs in the same spreadsheet. This separation is useful: people can fill out a form-like table without needing to type or remember approved values, while an editor can update the list in one central place. If a source value changes, the dropdown uses the current contents of the source range rather than a separate, manually duplicated list.

A dropdown is a data-entry aid, not a filter or a formula. Choosing an item places that value in the cell. It does not automatically change other cells, create a report, or prevent someone with sufficient editing access from changing the validation setup.

Create a dropdown from a range

In Google Sheets in a web browser, set up the rule as follows:

Put the allowed choices in a range, preferably in a clearly named area or on a separate tab. Use one option per cell, such as Lists!A2:A5.
Select the cell or group of cells where people should choose an option. You can select a whole column section if new rows will need the same dropdown.
Open Data > Data validation, then choose Add rule in the panel.
Under Criteria, choose Dropdown (from a range). This is different from Dropdown, which lets you type the choices directly into the rule.
Enter the source range or use the range-selection control to select the cells. Confirm that the range shown is the one containing your list.
Review the rule’s advanced options, then choose Done. Click a validated cell to check that the expected choices appear.

For a source tab named Lists, a reference might be Lists!A2:A5. If the tab name contains spaces, Sheets typically displays it with single quotes around the tab name, as in ‘Project Lists’!A2:A5. Using the range picker is a convenient way to avoid typing a reference incorrectly. The exact panel wording can change as Google updates Sheets, but the key choice is Dropdown (from a range).

Choose and organize the source range

Use a range that contains only the values you intend to offer. A header such as Status is usually best kept outside the options themselves; otherwise it can appear as a selectable choice. Similarly, remove stray spaces, duplicate entries, and notes from the source cells. A dropdown reflects the cell contents, so a misspelled or duplicated source value can confuse people using the sheet.

For a short, stable list, a compact range such as Lists!A2:A6 is easy to inspect. If the list will grow, you can select a larger range or an open-ended column range such as Lists!A2:A. That gives future entries room to be included without editing the rule, though it also means the source range covers empty cells below the current list. Keep the source column reserved for dropdown options so unrelated values do not become choices.

To use values from another tab in the same spreadsheet, choose that tab in the range picker or enter its reference. A range in a different spreadsheet is not simply a same-file tab reference. If the choices live in another file, bring them into the current spreadsheet first, for example on a helper tab, and use the local range for validation. An imported list may require access authorization and can depend on the source file being available.

Keep choices current with a helper list

A manually maintained source range works well when the allowed choices are known and change infrequently. For lists that should reflect entries elsewhere in the workbook, you can build a helper list with a formula, then use the cells containing its results as the validation source. Keep the helper area clear so spilled results have room to appear; values or other content in the way can prevent an array result from expanding.

For example, suppose category entries are in A2:A, including repeated values and occasional blank cells. With that layout, enter this formula in D2 to create a sorted, de-duplicated helper list:

=SORT(UNIQUE(FILTER(A2:A,A2:A<>"")))

With sample entries Blue, Red, Blue, a blank, and Green in A2:A6, the helper list returns Blue, Green, and Red in D2:D4. Set the dropdown source to the output range, leaving space for additional results if the source data may grow. This approach separates the raw entries from the clean choices. If there are no nonblank entries at all, the filter may have no results, so make sure the source data contains at least one valid option before relying on the dropdown.

Formula-generated lists are useful, but they add a dependency: if the helper formula is deleted, blocked, or points to the wrong data, the dropdown list may no longer behave as intended. For a small fixed list, entering values directly in a dedicated source range is often simpler to maintain.

Set validation and display behavior

The validation rule can determine what happens when someone types a value that is not in the source list. In the rule’s advanced options, Sheets offers a choice to reject invalid input or show a warning. Rejecting input is appropriate when the cell must contain an approved choice. A warning allows the value but flags that it does not match the rule, which can be useful when exceptions are legitimate.

Sheets also offers dropdown display styles, including chip, arrow, and plain text. The best choice depends on how the sheet is used. Chips are visually prominent in a status board, while an arrow can make the control’s purpose obvious in a compact table. Plain text uses less visual decoration. These choices affect appearance, not the underlying source values or whether the cell accepts invalid data.

Think about who can edit the source list as well as who can use the dropdown. If collaborators should select values but not change the approved options, protect the list range or limit editing access where appropriate. A validation rule alone does not secure the source list or make a spreadsheet read-only.

Practical examples and when to use this feature

Range-based dropdowns are useful anywhere consistency matters more than free-form typing. Examples include project statuses, department names, product categories, request priorities, and survey response choices. A shared list also makes it easier to update a choice consistently across multiple validated columns or tabs: edit the source cells, rather than visiting each rule to replace a manually typed set of options.

They are especially helpful when a list is longer than a few options or needs regular maintenance. For a one-off dropdown with just two or three stable choices, typing the items directly into a Dropdown rule may be quicker. For a standard status list used throughout a workbook, a separate source range is usually easier to audit and reuse.

Do not assume that changing the source list will rewrite values already selected in the destination cells. If a person previously chose Archived and you later remove Archived from the source range, existing cells may still contain that text. Review old entries if the allowed choices change, and decide whether to update those records manually. The rule controls future entry; it is not a cleanup command for historical data.

Common problems and how to fix them

The dropdown arrow or choices do not appear: Select a cell that is actually covered by the rule, then inspect Data > Data validation. Confirm that the rule exists and that its source range points to the intended cells.
The list contains a heading or unrelated values: Adjust the source range to exclude the header and any extra cells. Check whether the referenced column is used for other information.
A newly added option is missing: Check whether the new value falls inside the referenced range. If the rule ends at A10, adding a choice in A11 will not extend that fixed reference automatically. Expand the range or choose an appropriate open-ended source range.
A valid-looking entry is rejected: Compare it with the source cell carefully. Extra spaces, different punctuation, or a different spelling can make entries distinct. Correct the source value or select the exact option from the dropdown.
The list changed, but old cells still show removed choices: Existing entries are not automatically replaced when you edit the source list. Find and correct those cells if they should follow the new set of choices.
A collaborator cannot change the options: Check whether the source tab or cells are protected and whether the collaborator has permission to edit them. Permission to use a dropdown does not necessarily mean permission to edit its source range.

If a sheet has several similar rules, inspect the selected cells before editing validation. A rule applied to a large range may affect more rows than expected. Make a copy before making broad structural changes to a shared workbook, especially if the source list is used in multiple places.

Range-based dropdowns versus typed options

Google Sheets provides two related approaches. With Dropdown, you enter the options in the rule itself. This is convenient for a short list that will rarely change and keeps the setup self-contained. With Dropdown (from a range), the choices live in worksheet cells. That takes a little more organization but makes the list easier to edit, review, and reuse.

Use a source range when options come from a maintained reference list, when the same choices are needed in several places, or when formulas should create the available options. Use typed options when the list is brief and there is no benefit to maintaining a separate set of cells. Neither approach is inherently more accurate: the quality of the choices depends on maintaining the rule and its values.

Frequently Asked Questions

Can a Google Sheets dropdown use a range on another tab?
Yes. A source range can be on another tab in the same spreadsheet. Select it with the range picker or use a tab-qualified reference, then check that the dropdown shows the expected options.

Will a dropdown update when I add a new option?
It will if the new cell is inside the range used by the rule. A fixed range does not automatically grow just because you type below it. Expand the range or choose a source range that includes the rows you expect to use.

Can I use a formula to create the dropdown choices?
Yes. Put the formula in worksheet cells to generate a helper list, then set the validation source to the cells containing its results. The formula belongs in the sheet, not in the dropdown’s range field.

Can I use a range from a different Google Sheets file?
Not as an ordinary range reference to another file. Import or copy the choices into the spreadsheet that contains the dropdown, then point validation at that local range. If you import data, access permissions and refresh behavior can affect the helper list.

Why does Sheets allow a value that is not in my list?
Review the rule’s invalid-data setting. If it is set to show a warning, users can enter a nonmatching value. Choose the reject option if entries must be restricted to the listed choices, and review any existing values separately.

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.