Building Dropdown Lists

A dropdown list gives a cell a small arrow that reveals a menu of allowed choices. Users pick instead of type.

This is the friendliest form of data validation:

• No typos — choices are pre-approved.
• Consistent spelling across the whole column.
• Faster data entry for repeated values like statuses or regions.

Behind the scenes, a dropdown is just a validation rule of type List.

The simplest dropdown uses values typed directly into the rule. In Excel's Data Validation dialog, choose Allow: List and in the Source box type:

Yes,No,Maybe

Separate items with commas. In Google Sheets, choose Dropdown and enter each option.

• Great for tiny, rarely-changing lists.
• Downside: editing the list means reopening the rule.

For longer or changing lists, point the Source at a range of cells. Type your options down a column, say F2:F6, then set the validation Source to:

=$F$2:$F$6

Now editing the cells in column F instantly updates the dropdown.

• Use absolute references so the source stays fixed.
• Keep the option cells on a tidy lookup tab if you like.

This is where named ranges and validation combine beautifully. Name your options range RegionList, then set the validation Source to simply:

=RegionList

The dropdown now reads clearly and is easy to maintain. Anyone reviewing the rule sees a meaningful name instead of a cryptic address.

• Works the same in Excel and Google Sheets.
• The name and the dropdown stay in sync.

Imagine a task tracker. On a helper tab, list statuses in A1:A4: Open, In Progress, Blocked, Done. Name that range StatusList.

Select your Status column, open Data Validation, choose List, and set Source to =StatusList.

Every status cell now offers the same four clean choices. Reports and COUNTIF formulas counting each status will never miss a misspelled entry.

If you add a new option to your source list, a fixed range like =$F$2:$F$6 will not include it. Two fixes keep the dropdown growing:

• Convert the source to an Excel Table and name the column — tables expand automatically.
• Or define a dynamic named range using a spill function such as =A2# in newer Excel.

In Google Sheets, pointing the source at a full column like F2:F captures future additions.

A dependent dropdown shows different choices based on another cell. Pick a country in one cell, and the city dropdown shows only that country's cities.

The classic Excel trick names each sub-list to match a category, then uses INDIRECT in the source:

=INDIRECT(A2)

If A2 holds France and a named range France lists its cities, the dropdown adapts. It is advanced but powerful.

By default a List rule still lets users type values manually. You control this:

• In Excel, the Error Alert set to Stop rejects anything not on the list.
• If you allow Warning, users may override the dropdown.
• In Google Sheets, choose Reject the input to enforce the list strictly.

For clean reporting, choose the strict option so only listed values can be entered.

The little arrow only appears when the cell is selected, and only if In-cell dropdown is checked (Excel) or the dropdown style is on (Sheets).

• If you see no arrow, reopen the rule and enable the in-cell dropdown option.
• Sheets lets you choose between an arrow chip and a plain validation.

This setting controls presentation only; the underlying allowed-values rule stays the same either way.

Because the dropdown is driven by =RegionList, maintenance is easy:

• Add or remove regions in the named range cells.
• If the range size changed, update the named range in the Name Manager (or use a table to avoid this).
• Existing cells keep their values even if you later edit the list.

One named source feeds every dropdown that references it — change once, update everywhere.

Dropdowns get even more useful when paired with lookup formulas. Pick a region from a dropdown in A2, then look up its sales:

=XLOOKUP(A2,RegionList,SalesList)

Because the dropdown guarantees A2 always holds a valid region, the lookup never fails on a typo.

• The dropdown controls input.
• The lookup reacts to the choice.

This pairing is the heart of interactive, formula-driven reports.

You created a named range RegionList for your options. What should the data validation List source be to build a dropdown from it?

You learned to build dropdown lists that keep data clean:

• Use a List validation rule with typed items, a cell range, or a named range.
• Drive a dropdown with =RegionList for readability and easy upkeep.
• Make lists grow with tables or full-column sources.
• Use Stop error alerts to enforce only listed values.

You have completed Named Ranges and Data Validation — clearer formulas and controlled, reliable input.