How To Implement Cascade Menus In Excel

In this article, I will tell you how to use the excel Data Validation tool, named range,  and INDIRECT function to implement cascade menus with examples, this feature is very useful when you are using excel.

1. Example Excel Cascade Menus Structure.

1. Below is the menu structure in this example, the first column is the main menu, the second column is the menu when you select the main menu, and the third column is the sub menus when you select one second level menu item.
   

   A	B	C
   Main Menu	Second Level Menu	Third Level Menu
   Electronics	Computers	Desktop
   		Laptop
   		Tablet
   	Phones	iPhone
   		Samsung
   	Accessories	Headphones
   		Chargers
   Home_Garden	Furniture	Living Room
   		Bedroom
   	Appliances	Refrigerator
   		Microwave
   Clothing	Men	Shirts
   		Pants
   	Women	Dresses
   		Skirt

2. Now, I will tell you how to create the above cascade menus step by step.

2. Define Named Range For Each Menu List.

1. First, we should define a named range for each second and third level sub menu list.
2. We use the Electronics‘s sub menu as an example, input the text Computers, Phones, Accessories in cell range A21:A23.
3. Select the cell range A21:A23, and click the Formulas tab on the Ribbon.
4. Then click the Define Name button in the Defined Names group, it will pop up the New Name dialog window.
5. Input the text Electronics ( the first main menu text ) in the Name text box.
6. Click the OK button to create the named range.
7. If it shows the error message which said the name already exist, then you can click the Name Manager button in the Defined Names group to delete the name object and then create it again.
8. Please note the named range’s name must equal to the parent menu item text, otherwise it can not implement the cascade menus.
9. After you create a named range, when you select the range, you can see the range name in the Name Box on the top left corner of excel.
10. Below is the named range Name, Value, and Cell Range map table.
    

    Name	Value	Cell Range
    Electronics	Computers Phones Accessories	A21:A23
    Computers	Desktop Laptop Tablet	B21:B23
    Phones	iPhone Samsung	C21:C22
    Accessories	Headphones Chargers	D21:D22
    Home_Garden	Furniture Appliances	A25:A26
    Furniture	Living Room Bedroom	B25:B26
    Appliances	Refrigerator Microwave
    Clothing	Men Women	A28:A29
    Men	Shirts Pants	B28:B29
    Women	Dresses Skirt	C28:C29

11. Below is the named range in excel worksheet, when you select cell range A21:A23, it will display the named range’s name ( Electronics ) in the Name Box on top left corner of excel.

    

3. Create Cascade Menus In Excel Based On The Above Data Set.

3.1 Create Main Menu List.

1. Select cell A31, then click the Data tab in excel Ribbon.
2. Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
3. In the Settings tab, select item List from the Allow drop-down list.
4. Input the text Electronics,Home_Garden,Clothing in the Source text box.
5. Click the OK button to save it.
6. Now when you select cell A31, it will show a drop-down arrow on the cell right side.
7. When you click the down  arrow, it will list the 3 main menu item in the list.

   

3.2 Create The Second Level Menu List.

1. Select cell B31, then click the Data tab in excel Ribbon.
2. Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
3. In the Settings tab, select item List from the Allow drop-down list.
4. Input the text =INDIRECT(A31) in the Source text box.
5. Click the OK button to save it.
6. Now when you select cell B31, it will show a drop-down arrow on the cell right side.
7. When you click the down arrow, it will list the sub menu items of the selected main menu.

   

3.3 Create The Third Level Menu List.

1. Select cell C31, then click the Data tab in excel Ribbon.
2. Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
3. In the Settings tab, select item List from the Allow drop-down list.
4. Input the text =INDIRECT(B31) in the Source text box.
5. Click the OK button to save it.
6. Now when you select cell C31, it will show a drop-down arrow on the cell right side.
7. When you click the down arrow, it will list the sub menu items of the selected second level sub menu item.