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. Here are some other ways to insert bullets in Excel. 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). Mantra for Today: Keep it Simple!!! You May Also like the following Excel Tutorials:
How to make multiple selections in a drop-down list in Excel. Creating Multiple Drop-down Lists in Excel without Repetition. Create an Excel Drop Down list with Search Suggestions. Get Multiple Lookup Values in a Single Cell. How to Make a Yes/No Drop-Down in Excel? How to Remove Drop-Down List in Excel?
How can I use this on Pivot table and count the values idividually, let say an item has more than one values selected from the dropdown. how can I segregate it on the pivot?
