Drop Down Categories / Subcategories in Excel & Google Sheets

This tutorial demonstrates how to make drop-down categories and subcategories in Excel and Google Sheets.

 

 

Add Categories to a Drop Down

In Excel, you can create a drop-down list with items from a range of cells. Say you want to make a drop-down list of mobile phone models. If your list is long, you can add categories to make it more readable. To group a list of phone models by brand, follow these steps:

1. Create a list of items grouped by category (phone models in brand groups). Insert a brand name first, then under it, each model. Add a few spaces in front of each model name. This visually indicates which models belong to which brand.
   For a quick way to insert spaces, try the REPT Function or VBA.

 

 

2. Select the cell where you want to insert a drop-down list (B1), and in the Ribbon, go to Data > Data Validation.

 

 

3. In the Data Validation window, choose List under Allow drop-down. Then click on the arrow next to the source box to select the range with list items.

 

 

4. Select the range of list items and their categories, then press Enter (or click the arrow on the right side).

 

 

5. Back in the Data Validation window, click OK to confirm.

 

 

As a result, you have a drop-down list with categories in cell B2. If you click on it, you see items grouped by category.

 

 

Drop Down Categories in Google Sheets

Indenting list items doesn’t work in Google Sheets. The drop-down list will display items without leading spaces. Instead, use a cascading drop-down list to create one drop down for brand and one for model.