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.
    how-to-implement-cascade-menus-in-excel-1
  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.
    how-to-implement-cascade-menus-in-excel-2
  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.

    excel cascade menus named ranges
    excel cascade menus named ranges

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.

    excel cascade menus - main menu
    excel cascade menus – main menu

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.

    excel cascade menus - sub menu
    excel cascade menus – sub 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.

    excel cascade menus - third level menu
    excel cascade menus – third level menu

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.