Google Sheets Dropdown Formula: How It Really Works

Google Sheets does not create a dropdown with a standalone cell formula: use Data validation to make the dropdown, and use a formula in a helper range if you want its choices to update automatically. In a browser, select the destination cell and choose Data > Data validation > Add rule, then set the rule to Dropdown or Dropdown (from a range).

Is there a dropdown formula in Google Sheets?

Not in the sense of typing a formula into a cell to turn that cell into a menu. A dropdown is a data-validation rule attached to a cell or range. The rule controls which entries a user can select or enter; the cell still holds the selected value, such as “Open,” “In progress,” or “Done.”

Formulas can still help build the menu. For example, a formula can collect, sort, or filter values in a helper column. You then point the dropdown rule at the cells containing those results. This distinction matters: the formula prepares the choices, while data validation displays and enforces the dropdown.

If you simply need a short, fixed list, you may not need a formula at all. Enter the choices in the rule itself. If the list changes often, comes from a table, or depends on another selection, a formula-generated helper range is usually easier to maintain.

How Google Sheets dropdowns work

A dropdown is a data-validation rule, not a special cell type. The rule can offer a list of items you enter manually or take its choices from a range in the spreadsheet. You can apply it to one cell, a group of cells, or an entire column segment.

To add one in Google Sheets in a web browser:

Select the cell or cells that should contain the dropdown.
Choose Data > Data validation > Add rule.
Under Criteria, choose Dropdown for choices entered directly, or Dropdown (from a range) to use cells in the spreadsheet.
Enter the options or specify the source range, then review the display and invalid-data settings.
Choose Done. Select a cell with the rule to open its menu and choose an option.

The available appearance settings can make the choices display as a chip, an arrow, or plain text, depending on the rule options shown in the current interface. Appearance changes how the menu looks, not what values the rule accepts. A dropdown can also be configured to reject values outside the list or to show a warning instead; choose the behavior that fits the sheet’s purpose.

Build a dropdown from a range

A range-based list is a good choice when the options already appear in cells. For instance, put the department names in H2:H5, select the cells where users will choose a department, and make H2:H5 the source for a Dropdown (from a range) rule. Editing a source entry updates the available choice without rebuilding the rule.

Keep source values together in a clearly labeled area, perhaps on a separate tab called Lists. That makes the form easier to use and reduces the chance that someone will overwrite its options. In the validation rule, enter the range using the sheet name and cell references shown by Sheets. If a sheet name contains spaces, the reference may need single quotation marks around the name, such as ‘Lists for staff’!A2:A10.

When you want the choices to grow, use a source range that includes room for future entries, such as Lists!A2:A100, rather than selecting only the current filled cells. Blank cells in a larger source range may appear as blank choices, however, so a helper list that removes empty entries can produce a tidier menu.

Use a formula to create dynamic choices

For a changing list, put a formula in an unused helper column and point the dropdown at the cells where the formula returns results. Suppose product names are in A2:A100 and you want an alphabetized list without duplicates. With product names in A2:A100 and the helper formula starting in B2, enter =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))). The result is a sorted list of distinct, nonblank product names that updates as the source data changes.

In the formula, FILTER removes blank source cells, UNIQUE keeps one of each value, and SORT puts the resulting list in order. The formula produces multiple results in the cells below B2, so those cells must be clear for the results to expand. Create the dropdown with Dropdown (from a range) and use B2:B as its source range. If the source data changes, the helper results recalculate and the menu reflects the available values.

For example, if A2:A100 contains “Cable,” “Adapter,” “Cable,” and a blank cell, the helper list contains “Adapter” and “Cable.” This is not the only way to make a list: a fixed list is simpler when its contents rarely change, and a helper formula is more useful when the source is maintained elsewhere in the sheet.

Create a dependent dropdown

A dependent dropdown changes its options according to a choice in another cell. One common example is choosing a department first and then choosing an employee in that department. Keep the source data in two columns: department in A2:A100 and employee in B2:B100. Put the selected department in D2, and reserve an empty helper area beginning at F2 for the filtered employee list.

With departments in A2:A100, employee names in B2:B100, the selected department in D2, and the helper formula starting at F2, enter =IFERROR(SORT(UNIQUE(FILTER($B$2:$B$100,$A$2:$A$100=$D$2,$B$2:$B$100<>""))),""). The formula returns distinct, sorted employee names for the department selected in D2 and excludes blank names. If no matching employee is found, IFERROR displays a blank instead of an error.

Set the employee dropdown’s source to F2:F. If D2 contains “Sales,” for example, and the matching source rows contain Lee, Morgan, and Lee, the helper list offers Lee and Morgan. Change D2 to another department and the helper results change, so the dependent dropdown uses the new list. Keep the helper cells clear below F2 so the results can expand.

This setup is useful, but it has a practical limitation: changing the parent selection does not necessarily erase a child value that was already selected. If someone chooses an employee and then changes the department, check that the existing employee still belongs to the new department. For important records, use clear labels and review the stored selections rather than assuming a dropdown will automatically correct earlier choices.

Choose between a fixed list and a formula-driven list

Use the simplest approach that meets the need. A manually entered dropdown is quick for a few stable choices, such as Yes and No. A range-based dropdown is convenient when options are maintained in cells and may be edited by other sheet editors. A formula-driven helper list is suited to choices that need sorting, deduplication, filtering, or dependence on another selection.

Manual Dropdown: Best for a short, stable set of options. It avoids extra helper cells, but someone must edit the rule to change the choices.
Dropdown (from a range): Best when the source list is already stored in cells. Updating a source value can update the menu, though you may need a sufficiently large range for new entries.
Formula plus range-based dropdown: Best when choices should be calculated from changing data. It needs space for the formula output and a clearly maintained source layout.

These methods can be combined. For instance, keep a full list on a Lists tab, use a formula on a form tab to remove blanks or filter by category, then use that output as the dropdown source. This separates the maintained source data from the list presented to users.

Common problems and how to fix them

The formula is in the cell, but there is no menu. A formula that returns text does not create a validation rule. Add the rule through Data > Data validation and set the criterion to Dropdown (from a range).

The helper formula shows an expansion error. The cells needed for its results may not be empty. Clear the cells beneath and beside the formula output, or move the helper formula to an unused area. Also check that the formula has enough room to return all matching choices.

Blank choices appear. The source range may include empty cells. Use a filtered helper list that excludes blanks, or narrow the source range to the filled portion. Remember that a fixed range can include unused rows if you selected extra space for future options.

A new source item does not appear. Check that the validation rule references a range that includes the new row. A rule tied to A2:A10 will not use an item entered in A11. Expand the source range or use an appropriate larger range, and confirm that the helper formula itself includes the new source row.

A typed value is rejected. The rule may be set to reject invalid input. If free-form additions are acceptable, edit the rule’s invalid-data behavior to show a warning instead; if consistent entries matter, keep rejection enabled and add the missing option to the source list.

The dependent menu is empty. Confirm that the selected parent value matches the source data exactly, that the source columns are correct, and that the formula output area is clear. Extra spaces or inconsistent spellings in source labels can prevent an exact match. Clean the source entries if they should represent the same category.

What dropdowns do—and do not—guarantee

A dropdown makes data entry more consistent, but it is not a substitute for checking the whole spreadsheet. A validation rule can restrict normal entry in the cells where it applies, yet a copied or pasted value, a changed rule, or an incomplete source range can affect the result. Review the validation range and its source list when the sheet’s layout changes.

Dropdowns are particularly useful for categories that need consistent wording: project status, region, product type, or request priority. They can make filtering and summaries more reliable by reducing variations such as “In progress,” “In Progress,” and “in-progress.” They do not automatically make a report, chart, or calculation; formulas or other spreadsheet features are still needed to analyze the selected values.

Google Sheets and Excel both offer data-validation dropdowns, but the menus and setup details differ. In Sheets, use the Data menu and its data-validation rule editor described above. Excel has its own Data Validation controls, so instructions for one program should not be assumed to match the other exactly.

Frequently Asked Questions

Can I put a formula directly in a Google Sheets dropdown?
No. The dropdown rule takes manually entered options or a cell range as its source. To calculate the choices, put a formula in helper cells and set the dropdown’s source range to those results.

How do I make a dropdown list update automatically?
Store the source values in cells, use a range that includes future entries, or create a formula-driven helper list that updates from the source data. Then use that range in a Dropdown (from a range) validation rule.

Can a Google Sheets dropdown depend on another cell?
Yes. Use a formula in a helper area to filter the choices based on the parent cell’s selection, then use the helper output as the child dropdown’s source range. Make sure the helper output has room to expand.

How do I remove a dropdown but keep the selected value?
Select the cell or range, open Data > Data validation, and remove the validation rule. Removing the rule is different from clearing the cell, so the existing value can remain.

Why can’t I see my new dropdown option?
Check whether the source range includes the row containing the new option and whether a helper formula is filtering it out. Also check spelling, blanks, and any filter condition that depends on another cell.

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.