Google Sheets Dropdown From a Table: How It Works

Yes. In Google Sheets, create a dropdown from table data by selecting the cells that need the menu, opening Data > Data validation, and choosing Dropdown (from a range) for the criteria. Point the rule at the cells containing your options; if the list is in a Sheets table, use its worksheet cell range rather than assuming the table name itself will work as the source.

What “dropdown from a table” means in Google Sheets

People use “table” to mean two different things in Sheets. They may mean an organized block of cells with headers, such as a list of departments in columns A and B, or they may mean a formal Google Sheets table created with Insert > Table. Either way, the dropdown rule needs a range of cells containing the allowed choices.

A dropdown is a data-validation rule applied to one or more destination cells. The source range is where the menu’s choices come from; the destination range is where someone selects a choice. For example, a worksheet might store product categories in Lists!A2:A and use that list to make cells in Orders!C2:C easier to fill consistently.

This is useful for standardized entries such as department names, project stages, locations, or product types. Without a dropdown, different people might enter “In progress,” “In Progress,” and “Progress,” creating separate values that are harder to filter and summarize.

How to create the dropdown from a cell range

Put each allowed choice in its own cell in a source list. A header can go in the first row, but do not include it in the dropdown range.
Select the destination cell or cells where people should make a selection. To apply the rule to a column, select the intended rows, or enter a destination range in the rule editor.
Choose Data > Data validation. In the side panel, select Add rule if needed.
Under Criteria, choose Dropdown (from a range). Enter or select the source range, such as Lists!A2:A20. If the source is on another sheet, include that sheet’s name in the range.
Review the choices and the invalid-data behavior. Choose whether to reject entries outside the list or show a warning, then select Done.

For a concrete example, put “Hardware,” “Software,” and “Services” in Lists!A2:A4. Apply a rule to Orders!C2:C100 and use Lists!A2:A4 as the source. Each destination cell will offer those three category values. Selecting the cells first matters: it determines where the rule is applied, not where the choices are stored.

Using a Google Sheets table as the source

If your choices live in a formal Sheets table, the list is still stored in worksheet cells. Identify the column’s actual cell range and use that range in the data-validation rule. For example, if the table’s Category values are in column A, with a header in A1, a source such as A2:A can include the data rows while excluding the header.

A table can help keep a dataset organized, but do not assume its table name or a column-style reference will be accepted in the dropdown’s range field. A cell reference is the straightforward choice for data validation. If the table is on a different sheet, use a reference such as Lists!A2:A, substituting your real sheet name and column.

Keep the source list separate from the cells users fill in. If the choices sit beside the entry form, someone may overwrite them by mistake. A dedicated Lists sheet makes the source easier to find and maintain; you can also protect that sheet or the list cells if other collaborators should not edit the options.

Keeping the choices current as the list grows

A fixed source such as Lists!A2:A20 is easy to understand, but it will not include a new choice entered in A21 unless you update the rule’s range. One simple approach is to use an open-ended column range, such as Lists!A2:A. It includes later entries in that column while still skipping the header, though blank cells in the source range do not create useful choices.

Another approach is to reserve enough rows in advance, for example A2:A100, and extend the range when needed. This is practical for a short, stable list. Whichever option you use, avoid selecting an entire column that includes the heading if that heading should not appear in the menu.

For a list that should be generated from another dataset, use a helper range. For example, if raw category entries are in A2:A and you want a sorted list without blanks, place this formula in D2:

With category entries in A2:A, enter =SORT(UNIQUE(FILTER(A2:A,A2:A<>""))) in D2. It returns the distinct, nonblank category values in sorted order. Set the dropdown source to D2:D, so the menu uses the helper results rather than the raw entries.

The helper formula updates when the source data changes, but it needs room to return its results: keep the cells below D2 clear. If another value blocks the output, Sheets reports that the array result could not expand. Also remember that the helper list is only as complete as its input range; if your source data extends beyond A2:A, adjust the formula’s range.

Choosing the right dropdown behavior

For a controlled form, rejecting invalid entries is usually the clearest choice: users must select one of the listed values. If the list is a guide rather than a strict rule, a warning can be more flexible because it alerts people to an unusual value without blocking it. Choose based on how the resulting data will be used, especially if reports depend on consistent categories.

Sheets also lets you choose how dropdown choices appear. Depending on the rule’s settings, the display can use a chip, an arrow, or plain text. Chips can make statuses easy to scan, while plain text may suit a compact spreadsheet. Display style affects appearance, not the source range or the values permitted by the rule.

Use a single-select dropdown when each row should have one clear value, such as one owner or one status. If a task can genuinely belong to several categories, consider whether separate columns or a linked detail table would make the information easier to filter. A comma-separated entry in one cell can be convenient, but it is less consistent for analysis than storing one value per cell or row.

Common problems and how to fix them

The header appears as a choice: Edit the source range so it starts below the header, for example A2:A rather than A1:A.
A new option is missing: Check whether the validation rule uses a fixed range that ends before the new entry. Expand the range or use an appropriate open-ended range.
The range is rejected: Check the sheet name, column letters, and row numbers. If a sheet name contains spaces, put it in single quotes, as in ‘Project Lists’!A2:A.
Someone can type a different value: Open Data > Data validation and check the invalid-data setting. A warning permits an out-of-list entry; rejecting input enforces the list.
The dropdown is blank or has unwanted blank choices: Inspect the source cells for empty rows and confirm that the rule points to the intended column. A helper range that filters blanks can help when the source is uneven.
The helper list does not appear: Make sure the formula’s output cells are unobstructed and that the validation range points to the helper column, not the original data.

If only some destination cells show the menu, inspect the rule’s apply-to range. A validation rule applied to C2:C20 will not automatically cover C21 just because a nearby table has grown. Extend the destination range or apply the rule to the additional cells. This is separate from extending the source range: one controls where users can select, while the other controls which choices they see.

Google Sheets tables versus Excel tables

Google Sheets and Excel both support dropdown lists based on cells, but their table features and reference systems are not identical. In Sheets, the dependable way to connect a validation dropdown to a list is to specify the list’s cell range. The fact that the data is formatted as a table does not remove the need to identify that range.

Excel for Microsoft 365 has its own Data Validation workflow and structured table references. Those references and table-expansion behaviors should not be copied directly into a Google Sheets rule. If a workbook is shared between the programs, check the validation source after importing or exporting: a list may display differently if its source reference or table feature did not transfer as expected.

For a simple list that rarely changes, a fixed range is often easiest to maintain. For a growing list, an open-ended range or a helper range is generally more convenient. For a list that depends on another selection—for example, showing cities based on a chosen country—the setup is more involved and may require separate filtered helper ranges or formulas. A basic range-backed dropdown does not automatically create that dependency.

Frequently Asked Questions

Can a Google Sheets dropdown use a table column directly?
Use the worksheet cell range occupied by the table column as the dropdown source. Do not assume the table’s name or a structured column reference will work in the validation range field.

Will the dropdown update when I add a new table row?
It will include the new value if the data-validation source range includes the cell where that value is added. A fixed range may stop short, so use an open-ended range or update the rule when the list grows.

Can the source list be on another sheet?
Yes. Choose Dropdown (from a range) and specify the source sheet and cells, such as Lists!A2:A20. Use the actual sheet name; names containing spaces need single quotes around the name.

Can I make a dropdown that changes based on another dropdown?
Yes, but it takes more setup than a standard list. You need a way to produce a separate set of valid choices for each selection, often using helper ranges. The basic dropdown rule does not infer those relationships from a table automatically.

Why does my dropdown allow a value that is not listed?
The rule may be set to show a warning instead of rejecting invalid input, or the cell may not have the rule you expect. Check Data > Data validation and confirm both the invalid-data setting and the destination range.

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.