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.

    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

Subscribe to learn more excel tricks.

We don’t spam!

Subscribe to learn more excel tricks.

We don’t spam!

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.