Google Sheets Dropdown From Another Sheet: Setup Guide

Yes. In Google Sheets, select the cell or cells for the dropdown, open Data > Data validation, choose Dropdown (from a range), and point the rule to a list of cells on another sheet tab. The dropdown then uses those cells as its choices, so you can maintain the list separately from the form or table where people make selections.

How a dropdown from another sheet works

A dropdown is a data-validation rule attached to one or more cells. Its choices can be typed directly into the rule or drawn from a range of cells. For a list stored on another tab in the same spreadsheet, the range is the source and the validated cell is where a user selects an item.

For example, a workbook might have an Orders tab and a Lists tab. The Lists tab could contain product names in column A, while users choose a product in column C of Orders. Keeping the choices on a separate tab makes the working sheet less cluttered and gives the spreadsheet owner one place to update the approved options.

This is still one Google Sheets file, not a connection to a different spreadsheet file. A range on another file is not simply addressed by naming its tab; importing values from a separate file requires a separate setup, such as bringing the data into the current spreadsheet. The steps below concern another tab in the same file.

Set up the dropdown step by step

Put the allowed choices in a single column or row on a separate tab. Give the list a clear heading, and keep the heading out of the range you plan to use.
Return to the tab where people will make selections. Select one destination cell or the full destination range, such as the cells in the Product column beneath its heading.
Choose Data > Data validation. In the data-validation panel, choose Add rule if a rule is not already being edited.
Check the Apply to range field to confirm the selected destination cells are correct.
Under Criteria, select Dropdown (from a range). Enter the source range or use the range selector to navigate to the other tab and select the list cells.
Review the dropdown display and invalid-data options, then select Done. Click a destination cell to confirm that its menu shows the expected choices.

If the tab is named Lists and the values are in A2 through A20, the source is the range Lists!A2:A20. If a tab name contains spaces, it is commonly written with single quotes around the name, such as ‘Product List’!A2:A20. The range selector is a useful alternative to typing: it helps avoid misspelled tab names and incorrect cell addresses.

Choose a source range that is easy to maintain

The best source range depends on how often the choices change. A fixed range such as A2:A20 is straightforward when the list is stable and you know how many rows it uses. If the list grows, remember to edit the validation rule so its source includes the added rows. Otherwise, new items below the specified range will not appear in the menu.

For a list expected to grow, you can select an open-ended column range, for example A2:A, if the range field accepts it in your spreadsheet. This includes the cells from row 2 downward, so newly added entries can be picked up without changing the endpoint. Keep the column dedicated to the list: unrelated notes or values lower down could become dropdown choices too.

Another option is to allow room for growth with a larger bounded range, such as A2:A100. That is easy to understand and avoids referencing an entire column, but it still has a planned limit. Whichever method you choose, avoid including the column heading. A heading such as “Products” is a label for the list, not an option users should select.

Keep the source values clean and consistent. Extra spaces, duplicate entries, inconsistent spelling, or blank rows can make a menu harder to use. Google Sheets may show empty-looking entries if the selected source range includes blank cells, so test the dropdown after setting it up. Place the list together without gaps when practical.

Keep choices current without breaking the rule

Changing a value inside the referenced source range changes the corresponding choice available from the dropdown. For example, editing “Blue pen” to “Black pen” in the source list updates what users can select. The validation rule points to cells, not to a frozen copy of the text, which is why a separate list is useful for ongoing maintenance.

Adding a new choice is different from editing an existing one. The new entry must fall inside the referenced range. If the rule points only to A2:A20 and the next item is entered in A21, expand the range or use a source range designed to include future entries. Removing a choice from the source does not necessarily clear that value from cells where someone selected it earlier. Review existing records if removing an option has operational importance.

For a shared spreadsheet, consider protecting the source-list cells from accidental edits. Use Data > Protect sheets and ranges to configure protection, then decide who should be able to change the list. Protection helps manage editing, but it is not a substitute for checking who has access to the spreadsheet. Test the dropdown with the permissions and account types that matter to your workflow.

Set how Sheets handles invalid entries

A dropdown can guide users without necessarily preventing every unlisted value. In the rule’s advanced options, Google Sheets offers the choice to reject input that does not match the list or to show a warning. Reject input is appropriate when entries must match a controlled set, such as a status field with fixed values. A warning is more flexible when an unusual but legitimate entry may need to be recorded.

The display style affects how the choice appears, not where the choices come from. Depending on the available options in the rule, a dropdown can appear with an arrow, as chips, or in a more minimal display. Chips can make statuses visually distinct, while a plain-looking cell may fit better in a dense table. Check the result in the actual sheet because long option names may be less convenient to scan.

Applying one rule to a range of destination cells is usually easier than building separate rules cell by cell. Before applying it broadly, confirm that every destination cell should use the same choices. If different rows need different lists, a single shared source range may not meet the need; separate rules or a more specialized dependent-dropdown design may be necessary.

Common problems and how to fix them

The range is rejected: Check that the tab name is spelled exactly as it appears, that the range uses valid cell references, and that a tab name with spaces is quoted correctly. Selecting the cells with the range picker can resolve typing mistakes.
The new choice does not appear: Confirm that the new source cell falls inside the range specified by the rule. If it is below the endpoint, expand the range or adjust the source design.
The dropdown contains a heading or blank choices: Edit the rule and narrow the range to the actual entries. Keep headings and unused rows outside the selected source where possible.
Users can type other values: Edit the validation rule and choose the option to reject invalid input rather than show a warning. Also check that the rule applies to the cells users are editing.
Some cells have no dropdown: Inspect Apply to range. A rule may have been added to only one cell or may have missed rows added later.
An old selection remains after a list edit: The source controls available choices; it does not automatically rewrite previously entered data. Review those destination cells and correct them if needed.

When troubleshooting, select a destination cell and reopen Data > Data validation to inspect its rule. If several rules overlap or the destination range is unclear, correct the range and test one cell before applying a change to the whole table.

When a named range or a different approach helps

A named range gives a source range a readable label, which can make a rule easier to understand than a cell address. To create one, select the source cells and use Data > Named ranges. Enter a name, check the range, and choose Done. You can then use that named range as the source when configuring Dropdown (from a range), if it is accepted in the range field. Names are especially helpful when several validation rules refer to the same list.

For a short list that rarely changes, typing choices directly into a dropdown rule may be simpler. That avoids a separate list tab, but maintaining the options in the rule can be less convenient when the same choices are used in multiple places. A range-based dropdown is generally easier to update centrally and makes the source values visible in the spreadsheet.

If the choices in one cell should depend on a selection in another cell—for example, choosing a department first and then seeing only that department’s employees—that is a dependent dropdown, not just a dropdown from another sheet. It requires organizing the source data and rules so each dependent list is associated with the relevant first selection. Start with a basic range-based dropdown first, then add that complexity only if the workflow needs it.

Google Sheets compared with Excel

The general idea exists in both Google Sheets and Microsoft Excel: a data-validation list can use values stored elsewhere. The menus and rule editors are different, however. The directions here use Google Sheets in a web browser, with Data > Data validation and the Dropdown (from a range) criterion. Do not follow Excel’s ribbon instructions when working in Sheets.

Google Sheets also stores the spreadsheet online and supports collaboration through sharing, while Excel has its own workbook and sharing workflows. If you move a file between the programs, check the validation rules afterward. Features and range references may not transfer exactly as expected, so test the choices in the destination program rather than assuming the dropdown behaves identically.

Frequently Asked Questions

Can a Google Sheets dropdown use a range on another tab?
Yes. Choose Dropdown (from a range) in Data > Data validation, then select or enter cells from the other tab in the same spreadsheet.

Do I need a formula to make a dropdown from another sheet?
No. For a simple list, point the validation rule directly at the source cells. A formula or more advanced setup is only needed for tasks such as creating a calculated list or a dependent dropdown.

Will new items added to the source list appear automatically?
They will if the new cells are within the range used by the rule. If the rule ends at row 20 and an item is added in row 21, expand the range or use a range that includes future entries.

Can I hide the sheet that contains the options?
You can hide a tab using its tab menu, but hiding it is mainly a convenience for the spreadsheet layout. It should not be treated as a security measure. Keep sharing permissions and protection settings appropriate for the data.

Why can someone enter a value that is not in the dropdown?
The rule may be set to show a warning rather than reject invalid input, or it may not apply to that cell. Check the rule’s invalid-data setting and its Apply to range field.

Can one dropdown list be used in several columns?
Yes. Apply the same validation rule to the destination cells that should share the choices, or create separate rules that point to the same source range. Confirm that the selected destination cells all need the same 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.