A drop-down list lets people pick from fixed choices, which keeps data clean and avoids typing mistakes.
Quick method
- Select the cell (or cells) for the drop-down.
- Go to Data → Data Validation.
- Under Allow, choose List.
- In Source, type the items separated by commas, such as
Open,In Progress,Closed. - Click OK.
Use a range as the source
Type your items in a column, then in Source select that range. This is easier to maintain. If you convert that range to an Excel Table, new items appear in the drop-down automatically.
A list that grows by itself
In Microsoft 365, type the spill formula =UNIQUE(A2:A100) in a helper cell. In Source, type =E2# (the cell with the formula plus #). The drop-down now updates when the list changes.
Dependent drop-downs
A second drop-down that changes based on the first is called a dependent list. A simple way is to name each sub-list after its parent item, then in the second list Source use =INDIRECT(B2), where B2 holds the first selection.
Stop invalid entries
On the Error Alert tab, choose Stop, so people cannot type anything outside the list.
Remove a drop-down
Select the cell, open Data Validation and click Clear All.