How to create List, Drop Down List in Excel

The following article shows how to create List, Drop Down List in Excel

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.

How to create List, Drop Down List in Excel Picture 1

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

How to create List, Drop Down List in Excel Picture 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.

How to create List, Drop Down List in Excel Picture 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:

How to create List, Drop Down List in Excel Picture 4

Click the icon How to create List, Drop Down List in Excel Picture 5 -> move to Sheet Du_Lieu -> select all provinces:

How to create List, Drop Down List in Excel Picture 6

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

How to create List, Drop Down List in Excel Picture 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.

How to create List, Drop Down List in Excel Picture 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 :

How to create List, Drop Down List in Excel Picture 9

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

How to create List, Drop Down List in Excel Picture 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 .

How to create List, Drop Down List in Excel Picture 11

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

How to create List, Drop Down List in Excel Picture 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:

How to create List, Drop Down List in Excel Picture 13

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

How to create List, Drop Down List in Excel Picture 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!