To create a drop down list in Excel, type your list items in a column, select the cell that should get the dropdown, go to Data > Data Validation, set Allow to List, and point the Source box at the cells holding your items. Click OK and the cell gets an arrow that opens your list. That is the whole mechanism, and every other kind of dropdown in Excel is a variation on it.
The reason dropdowns matter on converted data is consistency. When a PDF report or statement lands in a spreadsheet, the description column is free text, and the moment you start adding your own category or approval column next to it, you get Fuel, fuel, FUEL, and Fuel Costs in the same column. Every one of those counts as a different value to a pivot table, a SUMIF, or a lookup. A dropdown makes the category column a fixed set of choices, so the totals you build on top of it actually add up.
How do I create a drop down list in Excel?
Type the choices somewhere first, one per cell, in a single column with no blanks. A spare sheet is the usual home for them. Then:
| Step | What you do |
|---|---|
| 1 | Select the cell or the whole column range that should have the dropdown |
| 2 | Data tab, then Data Validation, then Data Validation again |
| 3 | On the Settings tab, change Allow from "Any value" to "List" |
| 4 | Click in the Source box and select your list cells, for example =Lists!$A$2:$A$9 |
| 5 | Leave "In-cell dropdown" checked, then click OK |
Two checkboxes on that Settings tab decide how strict the list is. In-cell dropdown is what draws the arrow, so if you clear it the rule still restricts entries but nobody sees a list. Ignore blank lets someone leave the cell empty without tripping the rule, which is what you want on a column you are filling in over time.
Do not include the header cell in the Source range. If you select A1:A9 when A1 says "Category", the word Category becomes one of the choices, and it will quietly show up in your pivot table later.
How do I add a drop down list without a source range?
You can type the items straight into the Source box, separated by commas: Yes,No,Pending. No equals sign, no quotation marks, just the values with commas between them. This is fine for a short, permanent list like Yes and No.
It has a hard limit worth knowing: the Source box holds 255 characters, so a typed list of long category names runs out of room fast. It is also invisible to everyone else. Nobody opening the file can see where those values came from, and changing them means reopening the dialog on every cell. For anything you will edit again, put the list on a sheet and point at it.
How do I make a drop down list expand automatically?
Put the source list in an Excel table. Select the list cells, press Ctrl+T, confirm the header, and give the table a name in the Table Design tab, for example Categories. Then in the Source box use INDIRECT with a structured reference:
=INDIRECT("Categories[Category]")
Tables grow when you add a row underneath them, so a new category appears in every dropdown pointed at the table with no dialog reopening. The INDIRECT wrapper is needed because the Source box will not accept a bare structured reference.
The older approach still works if you cannot use a table:
=OFFSET(Lists!$A$2, 0, 0, COUNTA(Lists!$A:$A)-1, 1)
COUNTA measures how many items exist and OFFSET returns a range that tall, so the list resizes itself. The tradeoff is that OFFSET is a volatile function: it recalculates on every worksheet change whether or not anything relevant moved. On one small list you will never notice. Across a large workbook it adds up, which is why the table method is the better default now.
Can a drop down list depend on another drop down?
Yes, and this is the pattern people usually want once they have more than a dozen categories. Pick a broad group in the first cell, and the second dropdown only offers the sub-items belonging to that group.
It works through named ranges. Name each sub-list with exactly the text of its parent choice: a range named Vehicle holding Fuel, Tolls, and Repairs, a range named Office holding Rent, Supplies, and Software. Then set the second dropdown's Source to:
=INDIRECT(A2)
INDIRECT turns the text sitting in A2 into a reference to the range with that name. The catch is that range names cannot contain spaces, so a parent choice of "Office Costs" needs a range named Office_Costs and a Source of =INDIRECT(SUBSTITUTE(A2," ","_")). The other catch is that changing the first dropdown does not clear the second one, so an old, now invalid, sub-choice can sit there looking legitimate. Circle Invalid Data, covered below, finds those.
How do I edit or remove a drop down list in Excel?
To change the choices, edit the source cells if you pointed at a range, and every dropdown using it updates. If you typed the items into the Source box, select a cell with the dropdown, open Data Validation again, and edit the comma-separated text there.
To remove a dropdown, select the cells, open Data Validation, and click Clear All, then OK. To remove every dropdown of the same kind at once, select one cell that has it, open the dialog, tick "Apply these changes to all other cells with the same settings", then Clear All. That checkbox is also how you fix a whole column when you got the Source range wrong the first time.
Deleting the cell contents with the Delete key does not remove the rule. The dropdown is a property of the cell, not of its value, so the arrow comes right back the next time someone clicks in.
How do I let people type entries that are not on the list?
The Error Alert tab controls that, and the Style dropdown is the switch:
| Style | What happens on a value not in the list | Use it when |
|---|---|---|
| Stop | Entry is rejected outright | The list is the complete, final set of choices |
| Warning | Asks to confirm, entry is allowed if they say yes | New categories are legitimate but should be deliberate |
| Information | Shows a note, entry goes in either way | The list is a suggestion, not a rule |
Clearing the Error Alert checkbox entirely has the same practical effect as Information: the dropdown becomes a convenience rather than a control. Which one you want depends on whether an off-list value is a mistake or a real new case.
Why is my drop down list not working?
A dropdown that refuses to appear or rejects a value that is obviously on the list almost always comes down to one of these:
| Symptom | Cause | Fix |
|---|---|---|
| No arrow appears | "In-cell dropdown" is unchecked | Data Validation, Settings, tick In-cell dropdown |
| Data Validation is greyed out | Sheet is protected, or the cell is in edit mode | Unprotect the sheet, or press Esc first |
| Rejects a value that is on the list | Trailing space in the source item or the typed value | Run the source list through TRIM |
| Rejects a number that looks right | Source number is text, entry is a real number | Match the types on both sides |
| List is empty | INDIRECT points at a name that does not exist | Check the name in Formulas, Name Manager |
| Pasting overwrites the rule | Paste carries the source cell's validation | Paste Special, Values only |
That last one causes more silent damage than the rest combined. Ordinary Ctrl+V copies validation along with the value, so one paste into a controlled column strips the dropdown out of every cell it touches, and nothing tells you it happened. If a column keeps losing its dropdowns, someone is pasting into it.
The trailing-space case is the same root cause behind lookups failing on converted data. A value carrying an invisible space at the end is not equal to the same value without one, so the validation rule says no, VLOOKUP says #N/A, and the cells look identical on screen. Running the source list through TRIM to strip stray spaces fixes the dropdown and the lookups in the same pass.
How do I find entries that break the rule?
Adding a dropdown to a column that already has data does not check what is already there. Existing values stay exactly as they were, valid or not, because validation only fires on new entry. This is the normal situation after a conversion: the data arrived first, you added the rule second.
Use Data > Data Validation > Circle Invalid Data. Excel draws a red oval around every cell whose current value would fail the rule, so a typo like "Fule" or a leftover value from a changed dependent dropdown becomes visible immediately. Clear Validation Circles removes the ovals once you have fixed them. It is the fastest audit there is on a category column you inherited.
Using a dropdown on data you converted from a PDF
The realistic workflow, when the rows came out of a PDF, goes in this order. Convert the file and get clean rows first: our PDF to Excel converter keeps the table structure so the description and amount columns land where they should. Then add your own Category column next to the extracted data, put a dropdown on it, and code the rows.
Do the cleanup before the coding, not after. If the amount column came in as text, a pivot table built on your freshly coded categories will count the rows instead of summing the money, and the dropdown will have been beside the point. Converting text to numbers first means the categories you assign are worth something the moment you assign them.
Once the column is coded and consistent, SUMIF totals one category at a time and reads exactly as you expect, because a dropdown-controlled column has no near-duplicate spellings for SUMIF to miss. That is the actual payoff: not the arrow in the cell, but the fact that everything you build on the column afterwards is trustworthy. If the source documents are invoices rather than reports, a tool that pulls each line item into a spreadsheet automatically gets you to the same coded starting point without the retyping.
Does a drop down list work in Excel on the web and on Mac?
Yes on both, with one gap. Creating and using dropdowns works in Excel for the web and Excel for Mac through the same Data, Data Validation path. On the web, dropdowns created in the desktop app work normally, and you can create new ones too.
The gap is that a source range on a different sheet has historically been the fragile part on older versions. Excel 2007 and earlier could not reference another sheet in the Source box at all, which is where the habit of using a named range for the source came from. Modern versions handle cross-sheet references directly, so =Lists!$A$2:$A$9 is fine, but the named range approach still reads better and survives someone renaming the sheet.
The short version
Data, Data Validation, Allow: List, Source: your cells. Put the source in a table and wrap it in INDIRECT if the list will grow. Use Warning instead of Stop if new values are legitimate. Run Circle Invalid Data on any column that had data before you added the rule, and check for trailing spaces the moment a value that is clearly on the list gets rejected.
Last updated July 2026.