Excel: Hierarchal drop downs in 5 ways

If people have long lists of data to enter, help them make their work faster through hierarchies in dropdown lists. Here I will go through 5 methods, some formula and other non-formula based approaches.

A common example of hierarchal data is location, where you could have Continent - Country - City. You don’t want someone choosing "France" for country and London in then "New York" for city. Entering across multiple columns is also known as dependent or cascading drop-down lists, but hierarchal lists though doesn’t need to only be in multiple columns though as we can also have methods with autocomplete as you type and branching questions in Microsoft Forms which links back to Excel, and one instance in Google Sheets.

Formulas used in the video:
Named range/Create from selection =INDIRECT(H6)
Parent node if using Table: =INDIRECT("Table name[#Headers]")
Child node if using Table: =INDIRECT("Table name["&H6&"]")
Note you should replace the cell ref H6 and the table name

Chapters
00:00 - Intro
00:43 - 1. Excel autocomplete
01:54 - 2. G Sheets autocomplete
03:03 - 3. Named range dependent list
05:35 - 4. Table: Auto grow & dependent
10:27 - 5. Forms branching questions