Dropdown Range in Google Sheets: Create and Manage Lists

In Google Sheets, a dropdown range is the group of cells that supplies the choices for a dropdown, or the group of cells where you apply the dropdown rule. To create one, select the destination cells, choose Data > Data validation > Add rule, then set the criterion to Dropdown (from a range) and select the cells containing your choices.

What a dropdown range means

The phrase can refer to two different ranges, so it helps to distinguish them before setting up a rule. The source range contains the available options. The destination range contains the cells where people will see and use the dropdown. For example, you might store department names in Lists!A2:A6 and apply the dropdown to the Department column in an entry sheet, such as Requests!C2:C100.

In that example, Lists!A2:A6 is the source range, while Requests!C2:C100 is the destination range. The source can be on the same sheet as the dropdown or on another sheet in the spreadsheet. Keeping choices on a separate tab is often useful because it keeps entry forms uncluttered and makes the list easier to maintain.

A dropdown is created through data validation. The rule tells Sheets what values a cell is allowed to contain and, optionally, how to display or handle values that do not match. The dropdown is a way to choose an allowed value; it does not create a separate database or automatically update related information elsewhere in the sheet.

How to create a dropdown from a range

Put each option in its own cell in a source range, such as Lists!A2:A6. Use a clear heading in A1 if helpful, but leave the heading outside the range you select.
Go to the sheet where people will make selections and select the destination cell or range. For example, select C2:C100 to add the rule to a column of requests.
Choose Data > Data validation. In the side panel, select Add rule if a rule is not already being created for the selected cells.
Under Criteria, choose Dropdown (from a range). Use the range selector to choose the cells containing the options. If entering a reference directly, use the sheet name and cell addresses, such as Lists!A2:A6; sheet names containing spaces are written with single quotation marks, as in ‘Product list’!A2:A6.
Review the display style and the setting for invalid data. Choose Done to save the rule.

After saving, a dropdown indicator appears when a destination cell is selected. Click it to see the choices, then select an option. The exact appearance can depend on the display style chosen in the rule. A rule applied to C2:C100 can usually be inspected or edited by selecting a cell in that range and opening Data > Data validation.

Choosing the source and destination ranges

Choose a source range that contains only the intended options. If the list is in A2:A6, selecting A1:A6 by mistake may add the heading as a choice. Similarly, selecting a large block of unrelated cells can introduce unwanted values into the menu. A compact, clearly labeled list makes later editing less error-prone.

Choose the destination range based on where users may need a selection, not just where data currently exists. If a form will grow, applying the rule to a reasonable number of future rows can save you from adding it repeatedly. Applying it to an entire column is another option, but it may be less tidy in a large, complex sheet. Consider how the sheet is used and whether other rules or data occupy those cells.

For example, a small expense tracker could keep categories such as Travel, Meals, and Supplies in Lists!A2:A4 and apply the dropdown to Expenses!B2:B200. The source range supplies the category names; column B is where each expense record receives a category. Changing a category in the source list can affect the choices available to users, so edit that list deliberately.

What happens when the source list changes

A dropdown based on a range is useful when the options may change. Add a new option within the referenced source range and it can become available in the dropdown. If your rule points to A2:A6 and you add an option in A7, that cell is outside the stated range; edit the rule to include A7, or choose a range that already allows for expected additions.

One practical approach is to reserve extra cells for future choices, such as A2:A30, while keeping the actual options together at the top. However, empty cells within a source range can make the choices less clear and may appear as blank entries in the menu. Keep the source range clean and check the dropdown after adding or removing options.

Removing an option from the source list does not necessarily erase that value from destination cells that already contain it. Those cells may retain the old text even though it is no longer an available choice. Review existing entries when retiring an option, especially if reports, filters, or formulas depend on consistent labels.

Display style and invalid entries

Google Sheets lets you choose how a dropdown appears, including a chip-style presentation and other display styles offered in the rule panel. A chip can make a selected category easy to spot, while a more understated display may suit a dense table. These display choices affect presentation, not the underlying purpose of the source range.

The rule also lets you decide what happens when someone enters a value that is not on the list. Reject input prevents an unlisted value from being accepted under the rule. Show a warning allows the value but flags it as outside the allowed choices. Use rejection when consistent entries are important, such as standardized status labels; a warning may be more appropriate when users sometimes need to record an exceptional value.

Data validation is not the same as protecting a sheet. It can guide or restrict entries in cells, but it does not by itself prevent someone with editing access from changing the rule, editing the source list, or making other changes to the spreadsheet. If you need to limit who can edit particular cells or sheets, review Google Sheets protection options separately and choose permissions suited to your situation.

Common dropdown range problems

The choices do not appear: Check that the destination cells have a saved rule and that the rule uses Dropdown (from a range). Confirm that the source reference points to cells containing the options.
The menu includes an unwanted heading: Edit the source range so it starts below the heading, for example A2 rather than A1.
A new option is missing: Check whether the new cell falls inside the source range. Expand the range in the rule if it does not.
There are blank choices: Look for empty cells included in the source range and remove them from the range or organize the list without gaps.
Old values remain in the destination: The source list controls available choices, but removing an option does not automatically clean up previously entered values. Find and correct those cells if needed.
Some destination cells have no dropdown: Confirm that the rule applies to the full intended destination range. Selecting only one cell before creating the rule will not automatically cover every row in the column.

When troubleshooting, inspect both ranges rather than assuming the list itself is the problem. Select a destination cell and open Data > Data validation to review the rule and its applied range. Then check the source cells directly for misspellings, blanks, unexpected values, or an incomplete reference.

Dropdowns from a range compared with manual options

Google Sheets also offers a Dropdown criterion where you type the options into the rule instead of pointing to a cell range. A manually entered dropdown can be convenient for a short list that is unlikely to change, such as Yes and No. A range-based dropdown is usually easier to maintain when the choices are numerous, shared across several columns, or updated over time.

For instance, if several sheets use the same list of project stages, keeping the stages in one source range gives you a central place to manage them. If each rule instead contains a separately typed copy, updating the choices may require editing multiple rules. On the other hand, for a two-choice field used only once, a separate source tab may add unnecessary complexity.

Both approaches create data validation dropdowns. Neither automatically sorts the options, assigns a meaning to each choice, or updates calculations based on a selection. Use formulas, filters, conditional formatting, or other sheet features separately if you need those behaviors.

Google Sheets versus Excel

The basic idea is familiar in both Google Sheets and Microsoft Excel: a list of allowed choices can be used to guide entries. The controls and terminology are not identical. In Google Sheets, create or edit the rule through Data > Data validation and select Dropdown (from a range). In Excel for Microsoft 365 on Windows, list validation is managed through Data > Data Validation, with a Source field for the allowed list. Instructions for Excel should not be followed as though they were Google Sheets menu paths.

Google Sheets works in a browser and stores the rule as part of the spreadsheet. Excel also has its own data-validation behavior, including differences among desktop, web, and Mac versions. If a file is converted or shared between the programs, inspect the dropdown rules in the destination program rather than assuming every setting will transfer identically.

Frequently Asked Questions

How do I apply one dropdown to an entire column?
Select the destination cells in that column before creating the rule, or edit the rule’s applied range to cover the cells you want. A practical range such as C2:C500 can leave a header row untouched while covering current and future records.

Can a dropdown use choices from another sheet?
Yes. Set the criterion to Dropdown (from a range), then select the cells on the other sheet with the range selector. A reference may look like Lists!A2:A10, or ‘Product list’!A2:A10 when the sheet name contains a space.

How do I add another choice later?
Add it to the source cells and check whether it falls within the range used by the rule. If your rule ends at A10 and the new choice is in A11, expand the source range in Data > Data validation so that it includes A11.

Can people type a value that is not in the dropdown?
That depends on the invalid-data setting. Reject input blocks values outside the rule; Show a warning allows them but marks them as invalid. Choose the behavior that fits how strictly the sheet needs to enforce standardized entries.

Why does deleting a list item not remove it from existing cells?
The source range provides the available choices; it does not automatically rewrite destination cells that already contain a removed value. Search the destination range for the old entry and update those cells if you need the data to match the revised list.

Can I use the same source list for multiple dropdowns?
Yes. Create separate validation rules for the different destination ranges and point each one to the same source range. This makes a shared list easier to maintain, while allowing each destination range to serve a different column or sheet.

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.