ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
2
How to Create a Drop-Down List in Excel
3
4
This workbook demonstrates two ways to create a drop-down list, using T-shirt sizes as the example. Both methods still work in every current version of Excel (Microsoft 365, 2024, 2021, 2019, 2016) and Excel for the web/Mac.
5
6
Method 1 — Data Validation with a Typed List
7
Best for short lists you won't edit often.
8
9
1. Click the cell (or select the range of cells) where you want the drop-down to appear.
10
2. Go to the Data tab on the ribbon, then click Data Validation.
11
3. On the Settings tab, under Allow, choose List.
12
4. In the Source box, type your options separated by commas — for example: XS,S,M,L,XL,XXL
13
5. Click OK. The cell now shows a small drop-down arrow with your options.
14
6. To edit the list later, click the cell again, reopen Data Validation, and change the Source text.
15
16
See it in action on the "Method 1 - Data Validation" tab.
17
18
19
Method 2 — Named Range on a Separate Data Sheet
20
Best for longer lists, or lists you want to protect from accidental edits.
21
22
1. Put your list of options on its own worksheet (this keeps it safe from being reordered or deleted by accident).
23
2. Select the cells containing the list.
24
3. Click in the Name Box (top-left corner, just left of the formula bar) and type a name for the list, e.g. ShirtSizes, then press Enter. This creates a named range.
25
4. Go to the sheet and cell where you want the drop-down. Click Data > Data Validation.
26
5. On the Settings tab, under Allow, choose List.
27
6. In the Source box, type an equal sign followed by the range name, e.g. =ShirtSizes
28
7. Click OK. The drop-down now pulls its options from the named range.
29
8. To add or remove items later: go to the Data sheet, select the new full range of items, click the Name Box, type the same name again (e.g. ShirtSizes), and press Enter. This redefines the named range and the drop-down updates automatically.
30
31
See it in action on the "Data" tab (the source list) and the "Method 2 - Named Range" tab (the drop-down).
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100