How to Create List, Drop Down List in Excel

This guide explains how to create List, Drop Down List in Excel, with clear steps, practical tips, and important checks before you begin.

1. Create a regular drop down list

For example, there is a data field of provinces and cities, creating a drop down helps the data entry process be quick.

1. Create a regular drop down list screenshot

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

1. Create a regular drop down list screenshot 2

Step 2: The dialog box appears on the Settings tab in the Allow section select List , Source section enter the names of the components located in the drop down, the components separated by commas.

1. Create a regular drop down list screenshot 3

Alternatively you do not need to enter data directly, you can enter the components of the drop - down on another sheet. For example: Create a Du_Lieu sheet with the following provinces:

1. Create a regular drop down list screenshot 4

Click the icon 1. Create a regular drop down list screenshot 5 -> move to Sheet Du_Lieu -> select all provinces:

1. Create a regular drop down list screenshot 6

The results are the same as when created with direct input.

1. Create a regular drop down list screenshot 7

2. Creating a list depends on another list

For example: Enter the province list and city list depending on the province.

To create a dependent list, you should enter data into the list in the second way.

2. Creating a list depends on another list screenshot 8

Step 1: Name the data regions.

Mandatory process of creating dependent lists you must name the relevant data areas:

+ The data area in the city of Quang Ninh province you highlight the data area from cell C2 -> cell C7 -> named QuangNinh (note the correct signs and capital letters, and do not contain space).

+ The data area from cell D2 -> cell D5 is named Hai Phong .

+ The data area from cell E2 -> cell E7 is named Thai Binh .

-> How to name as follows: Right-click on the data area you want to name -> Choose Define Name :

2. Creating a list depends on another list screenshot 9

- A dialog box appears enter the corresponding name for the region as specified above:

2. Creating a list depends on another list screenshot 10

Please pay attention to the name so that it is the same as the value in the province name (but does not contain spaces).

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

2. Creating a list depends on another list screenshot 11

Step 3: A dialog box appears in Allow, select List , Source in the following formula: = INDIRECT (SUBSTITUTE (C15, "", "")) .

2. Creating a list depends on another list screenshot 12

You notice in this step on the formula to the relative address otherwise the value between cities does not change.

Step 4: Click OK to get the results:

2. Creating a list depends on another list screenshot 13

Similarly copy the formula for the remaining cells we have the results:

2. Creating a list depends on another list screenshot 14

So you've created 1 list depends on another list. In this article, use the INDIRECT function in combination with the SUBSTITUTE function to get the value of the province name that has removed the space to refer to the data area with the same name as the reference value.

For example when the list provinces take the results of Thai Binh SUBSTITUTE perform delete spaces => reference value becomes thaibinh -> references the data area named thaibinh => The return value is the district in the province of Status Average from cell E2 -> cell E7 in the named data sheet.

Good luck!