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!
You should read it
- How to create a drop list in Excel 2016
- How to create drop down list to select in excel
- How to Create a Drop Down List in Excel
- How to Create a List in Cells in Excel
- Instructions on how to create drop down lists or drop-down lists in Excel
- How to create drop down lists on Google Sheets
- How to create bullet list in Excel
- How to create List Box in Excel
- How to create drop down lists in Excel
- Calculate the subtotal of the list on Excel
- How to create a list of 'dream' App Store apps on iPhone
- How to create a filter in an Excel cell
May be interested
SUBTOTAL function - The calculation function for a group in a list in Excel
2 software to find and remove passwords on Word and Excel
Instructions for rounding decimal numbers after commas in Excel
Reduce the size of PDF files with Free PDF Compressor
Draw a mind map in Word
How to draw tornado charts in Excel