dynamic dropdown in excel

Now for something really cool: Make a drop-down menu that changes dependent on what you choose in another cell. It only takes five minutes!

1. Enter the data (2 minutes)

The main category should be in column A and the sub-categories in the other columns.
Important: The headline of each sub-category must be exactly the same as the name in the main category!

2. Name the ranges (1 minute)

Select all the entries in a column and use the shortcut Ctrl+Shift+F3 to create a name for the range. Repeat this with every column.

3. Make the drop-down menu for the main categories (1 minute)

Select a cell, e.g. I3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and press F3 to open the Paste Name window. Choose Categories.

4. Make the drop-down menu for the sub-categories (1 minute)

Select a cell, e.g. J3, and choose Data Validation on the Data tab (or shortcut Alt+D+L). Choose List, put the cursor in the Source field and type this formula:
=INDIRECT(I3)

We have created a dynamic drop-down menu! Can you imagine any easier way to impress your colleagues?