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.
- 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 - Now, I will tell you how to create the above cascade menus step by step.
2. Define Named Range For Each Menu List.
- First, we should define a named range for each second and third level sub menu list.
- We use the Electronics‘s sub menu as an example, input the text Computers, Phones, Accessories in cell range A21:A23.
- Select the cell range A21:A23, and click the Formulas tab on the Ribbon.
- Then click the Define Name button in the Defined Names group, it will pop up the New Name dialog window.
- Input the text Electronics ( the first main menu text ) in the Name text box.
- Click the OK button to create the named range.
- 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.
- Please note the named range’s name must equal to the parent menu item text, otherwise it can not implement the cascade menus.
- 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.
- Below is the named range Name, Value, and Cell Range map table.
Name Value Cell Range Electronics Computers
Phones
AccessoriesA21:A23 Computers Desktop
Laptop
TabletB21:B23 Phones iPhone
SamsungC21:C22 Accessories Headphones
ChargersD21:D22 Home_Garden Furniture
AppliancesA25:A26 Furniture Living Room
BedroomB25:B26 Appliances Refrigerator
MicrowaveClothing Men
WomenA28:A29 Men Shirts
PantsB28:B29 Women Dresses
SkirtC28:C29 - 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.
excel cascade menus named ranges
3. Create Cascade Menus In Excel Based On The Above Data Set.
3.1 Create Main Menu List.
- Select cell A31, then click the Data tab in excel Ribbon.
- Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
- In the Settings tab, select item List from the Allow drop-down list.
- Input the text Electronics,Home_Garden,Clothing in the Source text box.
- Click the OK button to save it.
- Now when you select cell A31, it will show a drop-down arrow on the cell right side.
- When you click the down arrow, it will list the 3 main menu item in the list.
excel cascade menus – main menu
3.2 Create The Second Level Menu List.
- Select cell B31, then click the Data tab in excel Ribbon.
- Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
- In the Settings tab, select item List from the Allow drop-down list.
- Input the text =INDIRECT(A31) in the Source text box.
- Click the OK button to save it.
- Now when you select cell B31, it will show a drop-down arrow on the cell right side.
- When you click the down arrow, it will list the sub menu items of the selected main menu.
excel cascade menus – sub menu
3.3 Create The Third Level Menu List.
- Select cell C31, then click the Data tab in excel Ribbon.
- Then click the Data Validation button in the Data Tools group to open the Data Validation dialog.
- In the Settings tab, select item List from the Allow drop-down list.
- Input the text =INDIRECT(B31) in the Source text box.
- Click the OK button to save it.
- Now when you select cell C31, it will show a drop-down arrow on the cell right side.
- When you click the down arrow, it will list the sub menu items of the selected second level sub menu item.
excel cascade menus – third level menu