Excel sets data validation 3 ways to implement a cell Drop-down menu
First, direct input:
1. Select the cells you want to set, such as A1 cells;
2. Select the "data" → "validity" of the menu bar to appear "Data Validation" pop-up window;
3. In the settings option → "Validity conditions" → "allow" → "ignore null" and "Provide pull-down menu" All tick → to the right, enter data under "source", such as "1,2,3,4,5,6,7,8,9" (excluding double quotes, split symbol "," must be half-width mode) → Press "OK" OK, select the A1 cell again, the Drop-down menu appears.
Ii. referencing data on the same worksheet:
If a column of the same worksheet is the data that the Drop-down menu wants, for example, the b2:b5,b2:b5 referencing the worksheet Sheet1 has the following data: 1, 2, 3, 4, as follows:
1. Select the cells you want to set, such as A1 cells;
2. Select the "data" → "validity" of the menu bar to appear "Data Validation" pop-up window;
3. In the settings option → "Validation conditions" → "allow" → "ignore null" and "Provide drop-down menu" All tick → "Enter data under source" = $B $: $B $ ", you can also press the direct selection b2:b5 area with red arrow to the right → Press Ok OK, select the A1 cell again, and a Drop-down menu appears.
Refer to the data in different worksheets (you must use the definition name):
If a column in a different worksheet is the data that the Drop-down menu wants, such as a A1 cell in worksheet Sheet1 to refer to the B2:b5 area of the worksheet Sheet2, the Sheet2 of the worksheet b2:b5 have the following data: 1, 2, 3, 4, as follows:
1. Definition Name: Menu bar → "Insert" → "name" → "Definition" → pop-up "define name" window, enter "DW" under "Name in Current Workbook" (you can do it yourself) → "=sheet2! $B $: $B $" Below "reference location", You can also press the "Add" button and press "OK" to complete the first step by pressing the B2:B5 area with the red arrow on the right.
2. Select the "data" → "validity" of the menu bar to appear "Data Validation" pop-up window;
3. In the settings option → "validity condition" → "allow" → "ignore null value" and "Provide Pull-down menu", all tick → enter "=dw" under "source", "DW" is the name just defined, press "OK" OK, select the A1 cell again, A Drop-down menu appears.