Creating Lists and Drop Down Menus in Excel

This article below provides guidance on crafting Lists and Drop Down Menus in Excel.

1. Creating a standard drop-down list

For example, when dealing with data fields like provinces or cities, implementing a drop-down list expedites the data entry process.

Step 1: Go to the Data tab -> Data Validation.

Step 2: In the dialog that appears under the Settings tab, choose List in the Allow field. In the Source field, enter the names of the components in the drop-down, separated by commas.

Alternatively, without directly entering data, you can input drop-down elements on another sheet. For example, create a sheet named Data with provinces:

The result is the same as creating it directly.

2. Creating a list dependent on another list

For example, inputting lists of provinces and cities dependent on provinces.

To create a dependent list, it's recommended to input data for the list using the second method. Consider having a data table:

Step 1: Name the data ranges.

It's essential to name the relevant data ranges during the process of creating a dependent list:

+ For the data range within Quang Ninh province, select the range from cell C2 -> C7 -> name it QuangNinh (pay attention to correct punctuation and uppercase, and avoid using spaces).

+ Name the data range from cell D2 -> D5 as HaiPhong.

+ Name the data range from cell E2 -> E7 as ThaiBinh.

-> To name, follow these steps: Right-click on the data range you want to name -> Select Define Name:

- The dialog appears, enter corresponding names for the ranges as specified above:

Make sure to name them similar to the province values (but without spaces).

Step 2: After naming the data, click on the cell where you want to create the list -> Go to the Data tab -> Data Validation.

Step 3: In the dialog under the Allow field, choose List. In the Source field, enter the formula: =INDIRECT(SUBSTITUTE(C15,' ','')).

Pay attention in this step; use the formula to maintain relative addressing, ensuring values between cities remain dynamic.

Step 4: Click OK to see the result:

Similarly, copy the formula for the remaining cells to achieve the desired outcome:

You have successfully created a dependent list using the INDIRECT function combined with SUBSTITUTE, extracting the province name without spaces to reference the data range with the same name.

For example, when the province list retrieves the result 'Thái Bình,' the SUBSTITUTE function removes the spaces => the reference value becomes ThaiBinh => referencing the data range named ThaiBinh => The returned values are the districts within Thái Bình province from cell E2 -> E7 in the named data sheet.

Wishing you all success!