Google Sheets Data Validation from a Range Explained

To create Google Sheets data validation from a range, select the cells that need a dropdown, choose Data > Data validation, set the criteria to Dropdown (from a range), enter or select the source range, then click Done. The dropdown choices come from the cells in that range, so changing the source list can update the available choices without rebuilding the rule.

What data validation from a range means

Data validation is a rule that controls or guides what people enter in a cell. With a dropdown from a range, the rule uses the contents of other cells as its allowed choices. For example, a task tracker can draw its status choices—Not started, In progress, and Done—from a small list on a separate worksheet. A user can then select one of those values rather than typing it from memory.

The source range is the set of cells that holds the options; the destination range is where people will use the dropdown. Keeping those ranges distinct makes the setup easier to understand and maintain. The source can be on the same sheet or another sheet in the same spreadsheet. This feature is especially useful when several columns or tabs need the same list, or when the list changes over time.

In Google Sheets, the rule adds a dropdown control to the destination cell. It does not copy the source cells into that cell, and it does not make the source list a separate database. The selected value is stored in the destination cell as ordinary cell content.

Create a dropdown from a range

In Google Sheets in a web browser, use these steps:

Put each allowed choice in its own cell in a source list. For example, enter Open, In progress, and Closed in Lists!A2:A4.
Select the destination cell or cells. You can select a whole range, such as B2:B100, if every row should use the same choices.
Choose Data > Data validation. In the data validation panel, choose Add rule if needed.
Under Criteria, choose Dropdown (from a range). Do not choose the plain Dropdown option if you want the choices to be drawn from cells.
Enter the source range, such as Lists!A2:A4, or use the range selector to select the cells directly.
Review the invalid-data behavior and display options, then click Done.

Click a destination cell to open the dropdown and select an option. The menu wording or panel layout can change as Google updates Sheets, but the key criterion is Dropdown (from a range). If you see an older-style rule editor, look for the same criterion in the criteria list.

Choose and enter the source range

A range reference identifies the source cells. For a list on the current sheet, a reference might be A2:A10. For a list on another sheet, use the sheet name and cell range, such as Lists!A2:A10. If a sheet name contains spaces or punctuation, enclose it in single quotation marks; for example, ‘Product options’!A2:A10. Selecting the cells with the range picker can help prevent spelling and punctuation mistakes.

Use a range that includes all intended choices but not unrelated notes or headings. If A1 contains the heading Status and A2:A4 contain the actual options, reference A2:A4 rather than A1:A4. Otherwise the heading may appear as a selectable choice. A bounded range, such as A2:A100, is straightforward and limits the source area. An open-ended column range, such as A2:A, can accommodate future entries, but empty cells or other content in that column may make the list less tidy. Check the dropdown after setting the rule.

For a short list that rarely changes, putting the source list on a clearly named tab such as Lists is often convenient. A separate tab keeps the working sheet uncluttered, and the source entries can be edited without changing the validation rule. If the spreadsheet has several lists, leave a blank column or otherwise separate them so one list does not accidentally include another.

Keep dropdown choices current

A range-based dropdown is useful because the options are maintained in cells rather than typed into each rule. If a choice in the referenced range is edited, the dropdown uses the edited value. If you add an option outside the referenced range, however, it will not be included automatically: expand the rule’s source range or define the original range broadly enough to include future entries.

For instance, if a rule points to Lists!A2:A4 and you type a new option in A5, edit the validation rule to include A5. To avoid frequent edits, you could use Lists!A2:A100, leaving space for more options. Keep that spare area reserved for the list, because any text entered inside the referenced cells could become a dropdown choice.

If you want a clean list that removes blanks and repeated entries, you can create a helper list in an unused column and use it as the dropdown source. Suppose the raw options are in Lists!A2:A, and you put this formula in Lists!C2: =SORT(UNIQUE(FILTER(A2:A,LEN(A2:A)))). It returns the nonblank entries once each, in sorted order, spilling the results down from C2. Set the validation source to Lists!C2:C. This formula is for Google Sheets; it is not a data-validation rule itself. Keep the cells beneath C2 clear so the results can expand.

Use a range on another sheet or a named range

Referencing another tab is a common way to keep the source list separate from the data-entry area. The range selector can be easier than typing a cross-sheet reference, particularly when sheet names include spaces. The source and destination still belong to the same spreadsheet file; a range-based dropdown is not a way to pull a live list directly from an unrelated spreadsheet.

For a workbook with multiple rules, a named range can make the source easier to recognize than a cell address. Create or manage named ranges through Data > Named ranges, then use a suitable named range as the validation source if it is accepted by the range field in your Sheets interface. A clear name such as StatusChoices is easier to interpret later than a long reference. If a named range is renamed, moved, or deleted, check the validation rule that depends on it.

Control invalid entries and display

The validation rule can either reject values that do not match the allowed choices or show a warning while allowing the entry. Choose rejection when consistent values matter—for example, when status values feed a summary or chart. A warning is more flexible when people may need to enter an exception, but it means the column can contain values that are not on the source list.

Google Sheets also offers display choices for dropdowns, such as showing an arrow or using a chip-style appearance, depending on the options available for the rule. These choices affect how the dropdown looks, not where its options come from. A person can still type into a validated cell, and the rule’s invalid-entry setting determines how Sheets handles a value that is not on the list.

Validation is a data-quality aid, not a security boundary. It should not be relied on to protect confidential information or prevent deliberate changes. For a shared spreadsheet, use appropriate sharing permissions and protect ranges when needed. Review the rule after importing or pasting data as well, since validation does not replace checking the actual values in a sheet.

Common problems and fixes

The dropdown does not show a new choice: Confirm that the new item is inside the source range. If the rule points to A2:A4, an entry in A5 is outside it until you expand the range.
The heading appears as an option: Exclude the heading row from the source range. Use A2:A10 instead of A1:A10 when A1 is a column title.
The source range is rejected: Check the tab name, exclamation mark, colon, and cell addresses. For sheet names with spaces, use single quotation marks around the name, or select the range with the range picker.
The dropdown contains an unexpected blank or choice: Inspect every cell in the referenced range, including cells below the visible entries if you used an open-ended column reference. Remove stray text or narrow the range.
Some destination cells lack the rule: Check the destination range in the rule panel. If new rows were added later, extend the destination range or apply the rule to the additional cells.
People can enter unlisted values: Edit the rule and choose the option to reject invalid input rather than only showing a warning. Then review existing cells separately; changing a rule does not necessarily clean up old entries.

When diagnosing a problem, select a cell that should have the dropdown and open Data > Data validation. Confirm both the destination range and the source range. This is more reliable than guessing from what is visible on the sheet, especially if multiple rules cover overlapping cells.

Range-based dropdowns compared with typed options

Google Sheets also lets you create a dropdown by entering options directly in the rule. That is a reasonable choice for a tiny, stable list used once. It avoids maintaining a separate source range. A dropdown from a range is usually better when the options need regular editing, are reused in several places, or should be visible as a maintained list on a dedicated tab.

The distinction is about where the options are managed, not whether the result is a dropdown. With typed options, you edit the validation rule to change the choices. With a range-based dropdown, you edit the source cells, provided the new item remains within the referenced range. For lists shared by multiple rules, a common source range helps keep spelling consistent and reduces repeated rule edits.

Excel has a similar data-validation feature, but its menus and details are not identical. In Excel for Microsoft 365 on Windows, the equivalent is under Data > Data Validation, where a list source is specified. Instructions for Excel should not be followed word for word in Google Sheets: use Sheets’ Data > Data validation menu and its Dropdown (from a range) criterion.

Frequently Asked Questions

Can Google Sheets use a dropdown list from another tab?
Yes. Put the choices on another tab and set the source to a reference such as Lists!A2:A20. If the tab name has spaces, quote it, for example, ‘Product options’!A2:A20. The range picker is a useful way to select it.

Does a dropdown update when I change the source cells?
Edits to cells already inside the referenced range are reflected in the dropdown. Adding an item outside that range does not automatically expand the rule, so either widen the source range in advance or edit the rule when the list grows.

Can I use a formula as the dropdown source?
Use cells containing the formula’s results as a helper range, then point the validation rule at those cells. For example, a formula can create a sorted, unique list in a spare column. The formula creates the choices; the validation rule still needs a range reference.

Why is my dropdown showing blank choices?
The source range may include empty cells, or an open-ended reference may include unused rows. Try narrowing the source to the populated cells, or create a helper list that filters out blanks.

Can I let people type a value that is not in the list?
Yes, if the rule is configured to show a warning rather than reject invalid input. If you need consistent categories, choose rejection instead. A warning is more permissive, but it can leave inconsistent values in the destination column.

Can I use the same dropdown in many cells?
Yes. Select the destination range before adding the rule, or edit the rule so its apply-to range covers all intended cells. A shared source range makes it easier to keep the choices consistent across those cells.

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.