Display Main and Subcategory in Drop Down List in Excel

Someone recently asked me if it was possible to get a main category and subcategory in a drop-down list in Excel, (something as shown below):

My first thought was to do this using VBA (if possible), but the answer is so easy, it surprised me.

Creating Subcategory in Drop Down List in Excel

All you need to do is to introduce a couple of spaces before the items/names of the subcategory. Now when you use this list (with spaces) to create a drop-down list, it will automatically show the indentation.

Here is how to do it:

  • Enter the main category in a cell.
  • In the cells below it, enter a couple of space characters and then enter the subcategory name. This gives an indentation to the text and it looks as if we have created a subcategory below the main category (as shown below):
  • Use these cells as the source while creating a drop-down list. The drop down will show the indentation as well.

Using Bullets to Show Subcategory in Drop Down List in Excel

In the above example, we have used space characters to show indentation. You can also use a bullet or dash to show subcategories.

Something as shown below:

Here are the steps to insert a bullet:

  • Go to the cell and double click (or F2) to get into edit mode.
  • Press Alt + 7 or Alt + 9 from your numeric keypad. It will insert a bullet in the cell.
    • If you don’t have a numeric keypad, activate the NumLock and follow the above steps.
  • Once you have a bullet in a cell, copy it and paste it in front of subcategories. To get the indentation, simply use the space character.
  • Now use the list with bullet points to create the drop down list. The drop down would now show a main category and sub-categories in the list (as shown below):

In this case, we have used bullets to show a sub-category. You can use any other symbol (such as increasing/decreasing, less than/greater than).

Add a Comment

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