Dropdown lists, from simple to self-sorting.

Typed straight into the validation rule

The simplest option: the choices are typed directly into the Data Validation dialog's Source box, comma-separated, with nothing held anywhere else in the workbook. There is nothing extra to maintain, but there is also no flexibility. Adding, removing or renaming an option means reopening Data Validation on every cell that uses it and editing the Source text by hand. Reasonable for a genuinely fixed, short list that is never going to change, such as a Yes/No or a small handful of fixed sizes.

Drawn from a range of cells

Instead of typing the choices into the validation rule itself, the Source box points at a block of cells holding them. Updating the list is now just a matter of editing those cells: no need to touch the validation rule again. The trade-off is that the range has to be set wide enough to allow for future additions, and any rows inside it that are still empty show up as blank entries at the bottom of the dropdown, which looks a little untidy until they are filled in.

Drawn from a range that resizes itself

COUNTA counts how many cells in the range actually contain something; OFFSET then builds a new range starting at the top of the list and exactly that many rows deep. Add a ninth item to a list of eight and the dropdown's range grows by one row automatically; delete one and it shrinks to match. No blank entries at the bottom, and no need to go back and widen the range by hand every time the list changes length.

Cascading: a list that cleans and sorts itself

The most capable version uses two dropdowns together. The first lets someone pick a category, Bikes or Accessories. The second then offers only the items belonging to whichever category was just chosen, already deduplicated and sorted, and updates the moment the first choice changes. Behind the scenes it takes four small steps:

The first dropdown's own list is just the two column headers, "Bikes" and "Accessories", sitting above the two raw data columns.

MATCH finds which of the two columns matches whatever was just picked in the first dropdown; INDEX then reads off how many items that column holds, from a small row of COUNTA counts kept above the raw data. This is the same auto-counting idea as the self-resizing list above, just aimed at whichever column is currently relevant.

Working from the inside out: OFFSET pulls the raw block of items belonging to the chosen category (the same MATCH trick picks the right column, and the helper count above sets how many rows deep to go). PROPER then fixes inconsistent capitalisation, so an entry typed as "HYBRID" reads back as "Hybrid". UNIQUE drops any entry that appears more than once in the raw data, and SORT puts what is left into alphabetical order. Written in one cell, this "spills" its results down into as many cells below it as it needs, without an array formula having to be copied down by hand.

ANCHORARRAY refers to the entire spilled range in one go, however many rows it currently occupies, rather than a fixed address. That is what makes the whole thing cascade: change the category in the first dropdown, the helper count and the spill formula both recalculate, the spilled range grows or shrinks, and the second dropdown's choices update to match, all without touching Data Validation again.

For this to work, the raw data behind it deliberately has to be a little messy, so it is worth seeing what it starts from. Bikes: Mountain, Road, Gravel, HYBRID, entered with inconsistent capitalisation on purpose. Accessories: Helmet, Lights, Lock, Helmet, with "Helmet" entered twice on purpose. PROPER and UNIQUE are what turn that untidy raw list into the clean, deduplicated one the second dropdown actually shows.