FormulaSmith
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,No                                  typed straight into the Source box — no range needed
=$H$2:$H$20                             a fixed range; another sheet works too: =Lists!$H$2:$H$20
=StatusList                             a 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 Excel18100Excel Drop Down List18100How to Add Drop Down List in Excel12100Create Dropdown in Excel9900How to Add Drop Down in Excel8100How to Insert Drop Down in Excel4400How to Create a Drop Down in Excel3600How to Create a Drop Down Menu in Excel3600How to Edit Drop Down List in Excel3600How to Change Drop Down List in Excel2900How to Insert Drop Down List in Excel2900How to Remove Drop Down List in Excel2900Drop Down Menu in Excel2900How to Add Drop Down Options in Excel2400Dependent Drop Down List2400Drop Down in Excel2400How to Do a Drop Down Menu in Excel1900How to Add Dropdown in Excel1600How to Delete Drop Down in Excel1600How to Delete Drop Down List in Excel1600How Do I Edit a Drop Down Menu in Excel1600How to Remove Drop Down in Excel1600How to Create a Drop Down Box in Excel1300Create Dropdown List in Excel1300How to Create a Dropdown Menu in Excel1300Editing Dropdown List in Excel1300Multi Select Dropdown1300How to Do a Drop Down Box on Excel1000How to Change Dropdown in Excel1000How to Change a Dropdown List in Excel1000How to Remove Drop Down Menu from Excel1000Excel Dropdown List1000Add Drop Down Menu in Excel880Insert Drop Down Boxes in Excel880How to Remove a Drop Down Menu in Excel880How to Remove Drop Down Box in Excel720Change Drop Down Excel720Excel Drop Down List with Color720Conditional Drop Down List Excel720How to Make a Drop Down Selection in Excel720How to Remove Data Validation Excel720Drop Down Menu in Excell720Excel Drop Down List Multiple Selection720How to Update Drop Down List in Excel720Edit a Dropdown List in Excel720How to Remove a Dropdown in Excel720Add Drop Down Box in Excel590How to Change Drop Down Box in Excel590Excel Edit Drop Down Box590List Data Validation in Excel590How to Delete a Drop Down Menu in Excel590Delete Dropdown in Excel590Dependant Drop Down List Excel590How to Remove Drop Down List from Excel590How to Modify a Drop Down List in Excel590Remove Dropdown from Excel590Delete Drop Down Box in Excel480How to Make Drop Down Boxes in Excel480How to Change a Drop Down Menu in Excel480Dependent Drop Down Menu Excel480Drop Down Menu Excel Multiple Selection480Excel Multi Select Drop Down480Select Multiple Dropdown Excel480Remove Drop Down Box from Excel390Drop Down Boxes in Excel390Cell Drop Down List Excel390Choose from a Drop Down List in Excel390How to Create Drop Down Options in Excel390Dependent Drop Down in Excel390Dependent Dropdown Lists in Excel390How to Do Drop Downs in Excel390Edit Drop Down Excel390Excel Drop Down List from Table390Linked Drop Down List Excel390Excel Multiple Selection Drop Down390Excel Multiple Selection Dropdown390How to Add a Drop Down Calendar in Excel320Add Dropdown List to Excel320Colored Drop Down List in Excel320Excel Drop Down List Colour320How to Create a Drop Down Table in Excel320How to Delete Data Validation in Excel320Dynamic Drop Down List Excel320Remove Drop Down from Excel320Excel Drop Down Select Multiple320Excel Yes No Drop Down320Drop Down Select Excel320How to Insert a Calendar Drop Down in Excel260How to Create a Drop Down Choice in Excel260Excel Clear Data Validation260Data Validation Drop Down in Excel260How to Use Data Validation in Excel260How Do You Do a Drop Down List in Excel260Editing Drop Down List in Excel260How to Get Rid of Drop Down in Excel260Insert Drop Down Menu Excel260How to Add Yes No Drop Down in Excel210Excel Calendar Drop Down210Dynamic Drop Down Excel210Excel Drop Down List FILTER210How to Add a Calendar Dropdown in Excel170Excel Drop Down Box170Excel Drop Down List Calendar170How to Make a Drop Down Cell in Excel170Data Validation Excel Drop-down List170Excel Data Validation Multiple Selections170Date Drop Down Excel170Excel Drop Down FILTER170Pick from a Drop Down List Excel170Excel Sheet Drop Down List170Drop Down Options Excel170How to Add Color to Drop Down List in Excel140How to Make Excel Cell Drop Down List140Changing a Drop Down List in Excel140How to Color Code Drop Down List in Excel140How to Copy a Drop Down List in Excel140How to Create Yes No Drop Down in Excel140Format Drop Down List in Excel140Excel Drop Down List NOT Working140How to Set a Drop Down List in Excel140How to Put a Drop Down Menu in Excel140Excel Yes OR No Drop Down140Excel Drop Down Selection140How to Add Name to Drop Down List in Excel110How to Add Yes OR No Drop Down in Excel110Add Yes No Dropdown in Excel110Excel Drop Down List from Another Sheet110How to Make a Dropdown Box in Excel110How to Get Drop Down Box in Excel110How Do You Put a Drop Down Box in Excel110How to Insert a Calendar Dropdown in Excel110Excel Column Drop Down List110Custom Data Validation in Excel110Dropdown Date in Excel110Formatting Drop Down List in Excel110How to Get Drop Down List in Excel110How to Get a Drop Down Menu in Excel110Excel Drop Down List Yes No110Excel Searchable Drop Down List110Updating Excel Drop Down List110Excel Drop Down Sort110Excel Add Drop Down List to Cell90How to Add Items to Drop Down List in Excel90How to Clear a Drop Down List in Excel90How to Create a Color Drop Down List in Excel90Conditional Data Validation in Excel90How to Create Pick from Drop Down List in Excel90How to Create a Yes/no Dropdown in Excel90Multiple Drop Down List in Excel90How to Put in Drop Down List in Excel90
Page 1 / 2Next

Other sub-topics

Cells · 497Remove Duplicates · 75Find Duplicates · 22Remove Blank Rows · 19Recover Unsaved File · 52Pivot Table · 68Fn VLOOKUP · 37Sort Data · 30Fn UNIQUE · 21Highlight Duplicates · 6Remove Spaces · 19PDF to Excel · 20