FormulaSmith
Free tools
Free tools

Drop-down Lists

One dialog builds every list on this page, so the useful question is not how to make a drop-down but which of these you are actually asking for. Three of the most requested — picking several values in one cell, colouring each choice, a pop-up calendar — are not settings anywhere in that dialog, and need a macro, a set of conditional-formatting rules and an ActiveX control respectively. Everything else comes down to where Source points. Items typed straight in cannot change without reopening the dialog, a fixed range stops dead at the row you gave it, and only a Table column, or a defined name over one, grows when entries are added later.

Back to DataAll guides

The formula

Yes,Notyped straight into the Source box — no range needed=$H$2:$H$20a fixed range; another sheet works too: =Lists!$H$2:$H$20=StatusLista defined name, so the list can point at a Table column=INDIRECT($A2)dependent list: shows the range named after whatever A2 holds=OFFSET($H$2,0,0,COUNTA($H:$H)-1,1)older-Excel range that grows as items are added below H2

A worked example

A sheet named Lists holds five statuses in H2:H6 — Draft, In review, Approved, Rejected, On hold. The drop-down belongs in C2:C200 of the Orders sheet.

Select C2:C200 > Data > Data Validation > Allow: List > Source: =Lists!$H$2:$H$6 > OK

Each cell in C2:C200 gains an arrow offering those five statuses, and anything else typed there is rejected with a stop message. Add a sixth status in H7 and the drop-downs ignore it — Source still ends at H6, which is exactly why a defined name over a Table column, or the OFFSET range above, is worth the extra minute up front.

Which one do I need?

If you want to…Use
You want a cell to offer a fixed set of choicesData > Data Validation > Allow: List, then type the items comma-separated or point Source at a range
The second list should only offer what fits the first (country, then city)Name one range per parent value, then set the child cell Source to =INDIRECT(A2)
Several items need picking in the same cellNot a validation setting — it takes a Worksheet_Change macro that appends each pick to what the cell already holds, and an .xlsm file
A plain Yes / No choiceType Yes,No directly into the Source box; a range is only worth it once the list is long or shared
Each choice should appear in its own colourData validation cannot colour anything — add one conditional-formatting rule per value over the same cells
The list of items lives on a different sheetPoint Source at =Sheet2!$A$2:$A$50; cross-sheet references work directly from Excel 2010 onward
You need to add, rename or reorder the items laterEdit whatever Source points at, not the cells themselves — the drop-downs read it live
The arrow has to goSelect the cells, Data > Data Validation > Clear All — Delete clears the value but leaves the validation active
The list should extend itself as rows are addedFeed Source from a Table column via a defined name, or use the OFFSET/COUNTA range
Hundreds of items, and scrolling is painfulA searchable list needs a source range driven by FILTER, or a combo box from Developer > Insert
You want to pick a date from a calendarThere is no built-in date picker — Allow: Date only restricts typing; a real calendar needs the ActiveX Date and Time Picker (32-bit Excel) or a userform
The arrow never appears, or the list comes up emptyCheck that In-cell dropdown is ticked, that the sheet is not protected, and that Source does not point into a closed workbook

Frequently asked questions

How do I remove a drop-down list from a cell?

Select the cells, open Data > Data Validation, click Clear All and then OK. Pressing Delete only empties the cell — the rule and its arrow stay, and the next value typed there is still rejected.

Can I choose more than one item from an Excel drop-down?

Not with data validation on its own; it accepts a single value per cell. Multiple selections need a Worksheet_Change event macro on the sheet that catches each pick and appends it to the existing contents, which also means saving the workbook as .xlsm.

Why is my drop-down arrow missing?

Three usual causes: In-cell dropdown was left unticked in the Data Validation dialog, the sheet is protected so the cell refuses input, or Source points into another workbook that is currently closed. The arrow also only shows once the cell is selected, so a printed or screenshotted sheet never shows it.

How do I make one drop-down depend on another?

Give each group of child items a defined name that matches its parent value exactly, with no spaces, then set the child cell Source to =INDIRECT(A2). Choosing France in A2 makes the second list show whatever the range named France contains.

How to Create a Drop Down List in ExcelExcel Drop Down ListHow to Edit Drop Down List in ExcelHow to Add Drop Down Options in ExcelExcel Drop Down List with ColorHow to Make a Drop Down Selection in ExcelDrop Down Menu in ExcellList Data Validation in ExcelDependant Drop Down List ExcelCell Drop Down List ExcelChoose from a Drop Down List in ExcelHow to Do Drop Downs in ExcelExcel Drop Down List from TableHow to Add a Drop Down Calendar in ExcelColored Drop Down List in ExcelExcel Drop Down List ColourHow to Create a Drop Down Table in ExcelHow to Add Yes No Drop Down in ExcelHow to Create a Drop Down Choice in ExcelData Validation Drop Down in ExcelHow to Use Data Validation in ExcelExcel Calendar Drop DownExcel Drop Down List FILTERHow to Make a Drop Down Cell in ExcelDate Drop Down ExcelPick from a Drop Down List ExcelExcel Sheet Drop Down ListDrop Down Options ExcelHow to Add Color to Drop Down List in ExcelHow to Color Code Drop Down List in ExcelHow to Copy a Drop Down List in ExcelExcel Drop Down SelectionHow to Add Name to Drop Down List in ExcelExcel Drop Down List from Another SheetHow to Get Drop Down List in ExcelExcel Column Drop Down ListExcel Searchable Drop Down ListExcel Drop Down SortHow to Add Items to Drop Down List in ExcelConditional Data Validation in ExcelHow to Create Pick from Drop Down List in ExcelMultiple Drop Down List in ExcelDrop Down Arrow ExcelExcel Multi Drop Down ListExcel Data Validation Date

Other sub-topics

Cells · 494Remove Duplicates · 62Find Duplicates · 32Remove Blank Rows · 19Recover Unsaved File · 52Highlight Duplicates · 9Pivot Table · 63Remove Data Validation · 20Sort Data · 30COUNT Duplicates · 15Fn VLOOKUP · 23Remove Spaces · 19