Data Validation in Excel permits to restrict choices in range. Here described how to set up lists in Excel which show restricted choices depending on earlier selection. In result, this makes tree of categories in validation ranges.

Instructions

Step 1

Make your lists.

Step 2

Make initial validation list. Click on "Data Validation". In "Allow", choose "List". Then choose range of probable options.

Step 3

Then, name every subset range to match choices in initial validation range. For this, highlight first range, then utilize Ctrl+F3 to show Name Manager. Click on "New", then type name of related option from first validation list.

Step 4

Do again Step 3 for remaining subset ranges, naming them to subsequent to other options in initial validation list.

Step 5

Highlight range where you want reliable validation choices to show. Click on "Data Validation", choose Allow List, and then in Source dialog box, type =INDIRECT(A2) and click "OK".

Step 6

Now, check spreadsheet to make sure validation settings are right. A menu must show in Column B, presenting options that realted to choice in Column A.