How to create a drop-down list in Excel, and make it update itself

A drop-down list puts a small arrow in a cell and lets people pick from a fixed set of options instead of typing. That one change removes a whole class of problems: "Norht" instead of "North", "yes", "Yes" and "Y" in the same column, and dates typed three different ways. Totals, filters, pivot tables and lookups all work better when the values are the same every time.

In Excel a drop-down list is a kind of data validation, a rule for what a cell accepts. This guide starts with the four steps to create one, then shows where to keep the options so the list stays up to date, how to make one list depend on another, and how to write custom rules with formulas for the cases a list can't cover.

What a drop-down list is

A drop-down list is a data validation rule of the type List. The rule lives on the cell, not in it, so the cell can still be empty, hold a value or be used by formulas like any other cell. When someone selects the cell, an arrow appears to the right. Clicking it shows the options, and picking one writes that value into the cell as plain text or a number.

The rule also checks what people type. If someone types a value that isn't in the list, Excel refuses it with an error message. That is what makes a drop-down list more than a convenience: the column only ever contains the values you allowed.

Create a drop-down list in four steps

1. Select the cells that should get the list, for example B2:B50.
2. Choose Data > Data Validation. It sits in the Data Tools group.
3. On the Settings tab, set Allow to List.
4. In Source, type the options separated by commas, for example Yes,No,Maybe, and click OK.

Every cell in the selection now has the list. Leave In-cell dropdown ticked, or the arrow won't appear. Ignore blank lets the cell stay empty without an error, which is usually what you want.

You don't need the mouse to pick. Select the cell and press Alt+Down Arrow to open the list, move with the arrow keys and press Enter to choose. You can also just type the option, and Excel accepts it as long as it matches one in the list, upper or lower case.

The separator is the list separator from the computer's regional settings, the same one you use between arguments in formulas. With US settings it is a comma. On a computer where formulas use semicolons, type Yes;No;Maybe instead.

Typed options are fine for short lists that never change: Yes and No, the four quarters, a handful of statuses. For anything longer, or anything that will change, put the options in cells.

A drop-down list from a range of cells

Type the options in a column, one per cell, for example in A2:A6 on the same sheet. Then in Source, click the box and select the range. Excel writes =$A$2:$A$6, with $ signs so every cell with the list points at the same options.

The list shows the options in the order they stand in the cells, so sort the range the way you want it to appear. Empty cells in the range become empty lines in the list, so leave no gaps.

This is the version most people should use. Changing an option means changing a cell, which anyone can see and do, and you can use the same range for many drop-down lists in the workbook.

A drop-down list from another sheet

Option lists clutter a sheet that people work in, so they often live on a separate sheet, for example one called Lists. You can point Source straight at it: click in the box, click the Lists tab and select the range. Excel writes a reference like =Lists!$A$2:$A$10.

Two things make lists on another sheet easier to live with:

• A named range. Select the options and type a name such as Products in the Name Box, left of the formula bar, then press Enter. You can also use Formulas > Define Name. Now Source can simply say =Products, which is easier to read and keeps working if the list moves.
• A hidden or protected list sheet. Once the drop-down lists work, hide the Lists sheet or protect it, so nobody edits the options by accident.

Make the list update itself

A drop-down list with the source =$A$2:$A$6 shows exactly those five cells. Type a sixth option in A7, and the list doesn't include it. There are two good ways around that.

Use an Excel table. Select the options and press Ctrl+T to make them a table. When you then point Source at the table's column, the reference grows with the table. Type a new option in the row under the table, the table expands, and the drop-down list includes it straight away.

Use a formula that builds the list. In Excel for Microsoft 365, a formula can spill a list into the cells below it. That is handy when the options already exist in your data, just with repeats, such as the products in a list of orders. SORT and UNIQUE give you each product once, in order:

To use the spill as the source, type =$D$2# in Source. The # after the cell means the whole spilled range, however long it is. Add an order for a new product, and it appears in the list without anyone touching the drop-down. Practice it in a drop-down source without repeats, and see SORT and UNIQUE for more on the two functions.

A dependent drop-down list

A dependent drop-down list changes with the choice in another cell. Pick Dairy in the first list, and the second only offers dairy items. Pick Fruit, and it offers fruit. It's the standard way to handle categories and subcategories, regions and stores, or departments and employees.

In Excel for Microsoft 365, FILTER builds the second list from the first choice:

Put the FILTER formula in a helper cell, point the second drop-down at it with =$E$2#, and the second list always matches the first choice. Try it in a list that depends on the first choice.

In older versions of Excel, without FILTER, the classic method uses names and INDIRECT. Make one list per category and give each the name of its category: a list of fruit named Fruit, a list of dairy items named Dairy. Then set the source of the second drop-down to =INDIRECT($A2), where A2 holds the first choice. INDIRECT turns the text in A2 into a reference to the range with that name. Names can't contain spaces, so a category like "Frozen food" needs a name like Frozen_food and a SUBSTITUTE inside INDIRECT, which is one reason the FILTER version is easier.

One thing neither method does is clear the second choice when the first one changes. If someone picks Dairy and Milk, then changes the first cell to Fruit, Milk stays in the second cell until someone picks again. A conditional formatting rule that flags the mismatch, or a note to the user, covers it.

Use the choice in a formula

A drop-down list is often the input to a calculation: pick a product and see its price, pick a month and see its total, pick an employee and see their department. The cell with the list holds an ordinary value, so any lookup can use it:

That pattern turns a sheet into a small tool: one drop-down list at the top, formulas below that react to it. Since the value can only be one of the options, the lookup never fails because of a typo. The lookups guide covers XLOOKUP, VLOOKUP and INDEX MATCH in depth.

Count and total by the choices

Once a column only holds the values from a list, summaries become simple. Put the options in a small table next to the data, and count how often each one was picked with COUNTIF, or add up an amount for each with SUMIF. With the statuses in D2:D4 and the picks in B2:B50, =COUNTIF($B$2:$B$50,D2) counts the first status, and filling it down counts the rest. =SUMIF($B$2:$B$50,D2,$C$2:$C$50) totals the amounts in column C for the same status.

Those formulas only work because every "Done" is spelled the same way. In a column where people typed freely, "Done", "done " and "Finished" would be three different values, and the count would be wrong without any warning. A pivot table works the same way: one row per option, with no surprises. If you inherit a column that was typed by hand, clean it first with the methods in the clean data guide, then add the drop-down list so it stays clean.

Input message and error alert

The Data Validation dialog box has two more tabs that make a drop-down list friendlier.

Input Message shows a small note when someone selects the cell, for example "Pick the region of the customer". It's a good place to explain what the column is for, especially in a shared sheet.

Error Alert decides what happens when someone types a value that isn't allowed. The Style has three options:

• Stop refuses the value. The person has to type something valid or cancel. Use it when the column must be clean.
• Warning asks whether to keep the value anyway, with Yes, No and Cancel. Use it when exceptions are allowed but should be rare.
• Information just tells the person and keeps the value.

Write your own title and message, such as "Unknown region. Pick one from the list." The default message says only that the value doesn't match the restrictions, which helps nobody.

Custom rules with formulas

Allow has more options than List: Whole number, Decimal, Date, Time and Text length each come with a minimum and maximum. They cover rules like "a quantity from 1 to 100" or "a date in 2026" without any formula.

When the rule is more specific than that, set Allow to Custom and write a formula. The formula is a test that returns TRUE or FALSE, written for the first cell of the selection. Excel accepts the entry when the test is TRUE and refuses it when it is FALSE. Nothing more is needed: no IF, no "Yes" or "No".

Only weekdays, for delivery or meeting dates:

No duplicates, for IDs, invoice numbers or email addresses. COUNTIF counts how many times the entry appears in the list, and because the entry counts itself, a new value gives exactly 1:

The list needs $ on both ends, so every cell in the selection checks the same list, while A2 moves with each cell. That is the same rule as in a formula you fill down.

Codes in capital letters only. Comparisons with = ignore case, so the rule needs EXACT, which compares letter for letter:

A fixed number of characters, for phone numbers, postal codes or account numbers. Text length can do this too, but a formula lets you combine it with other tests:

A total that must stay within a budget. The rule can look at other cells than the one being typed in, so it can refuse an entry that would push the total over the limit:

A few more rules that come up often:

• Must contain @, for email addresses: =ISNUMBER(SEARCH("@",B2)).
• Must start with a prefix, such as KB- for keyboards: =LEFT(A2,3)="KB-".
• No dates in the future: =B2<=TODAY().

Practice the rules in weekdays only, block duplicates and codes in capitals only.

Edit, copy and remove a drop-down list

To change a list, select one of its cells and open Data > Data Validation again. Tick Apply these changes to all other cells with the same settings before you click OK, and Excel updates every cell that uses the same rule, not just the one you selected.

To give more cells the same list, copy a cell that has it, select the new cells and use Paste Special > Validation. That copies the rule without the value. A normal paste copies the rule too, but also the value and the formatting.

To find the cells with a rule, use Home > Find & Select > Data Validation. It selects every cell on the sheet that has one, which is useful in a workbook someone else built.

To remove a list, select the cells, open Data Validation and click Clear All. The values already in the cells stay, only the rule goes.

When a drop-down list doesn't behave

• The arrow doesn't show. It only appears when the cell is selected. If it's missing even then, In-cell dropdown has been unticked, or the rule has been removed.
• Pasted values get past the rule. Validation checks what people type, not what they paste. Pasting a value into the cell replaces the rule along with the value. If the column must stay clean, protect the sheet or check it now and then.
• Old values break the rule. Adding a rule doesn't check what is already in the cells. Open the arrow next to Data Validation and choose Circle Invalid Data to mark every cell that breaks its rule.
• The list has empty lines. The source range includes empty cells. Shrink the range, use a table, or build the list with UNIQUE and FILTER.
• The list is too long to scroll. A drop-down list with hundreds of options is hard to use. Split it into two dependent lists, or sort it so people can find their option.
• Typed options stopped working. Typed options in Source can hold at most 255 characters. Move a long list into cells.

Drop-down lists in Google Sheets

Google Sheets has drop-down lists under Insert > Dropdown, and the full set of rules under Data > Data validation. Choose Dropdown to type the options or Dropdown (from a range) to point at cells. Sheets shows each choice as a colored chip by default, and you can pick a color per option, which makes status columns easy to scan. Custom formula rules work the same way as in Excel: a test that returns TRUE or FALSE, written for the first cell of the range.

Drop-down lists in Excel tests

Employer Excel tests often ask you to "restrict the Region column to North, South, East and West" or "add a drop-down list using the values in the Lists sheet". Read whether the task names the options or points to a range, because a range is usually what the checker expects. Select the whole range before you open Data Validation, so the rule covers every row. And if the task mentions an error message, set the Style and write the text, since that is often part of the marking.

In short

A drop-down list is a data validation rule of the type List: Data > Data Validation > Allow: List, with the options typed in Source or, better, taken from a range of cells. Keep the options on their own sheet, give them a name, and use an Excel table or a spilled SORT and UNIQUE list so the drop-down updates itself. FILTER, or INDIRECT with named ranges in older versions, makes one list depend on another. And when a list isn't the right rule, a custom formula that returns TRUE or FALSE can allow weekdays only, block duplicates or require capitals.

The formulas are where most of the skill sits, and they are what the exercises below train: the source list without repeats, the dependent list and three custom rules. Try the first one for free and write the formula yourself.