Docs tower · floor

How to make a drop down list in Excel, and make it hold

The steps for how to make a drop down list in excel are documented and short. Two decisions inside them determine whether the list is still working in six months, and both are easy to miss — one is in a note, the other is on a tab most people never open.

“When your data is in a table, then as you add or remove items from the list, any drop-downs you based on that table will automatically update. You don’t need to do anything else.”

Microsoft’s own note, read 22 August 2026. This is the difference between a drop-down you will be maintaining in a year and one that maintains itself. Put the source list on its own sheet, select it, and press Ctrl+T before you do anything else — Microsoft gives that shortcut on the same page.

The steps, from Microsoft

The list of options lives somewhere else in the workbook; the drop-down is a rule applied to a cell that points at it.

“In a new worksheet, type the entries you want to appear in your drop-down list.” Then Ctrl+T to make it a table.
“Select the cell in the worksheet where you want the drop-down list.”
“Go to the Data tab on the Ribbon, and then Data Validation.”
“On the Settings tab, in the Allow box, select List.”
“Select in the Source box, then select your list range.” Microsoft’s own example leaves out the header row, “because we don’t want that to be a selection option”.
“If it’s OK for people to leave the cell empty, check the Ignore blank box.” Then check In-cell dropdown.

One diagnostic worth knowing before you start, because Microsoft gives it: “If you can’t select Data Validation, the worksheet might be protected or shared.” A greyed-out command is usually protection rather than a fault.

Notice what the fifth step implies. The drop-down does not contain the options — it points at them. The list lives in cells somewhere else in the workbook, and the validation rule is a reference to that range. Which is why a table matters: a plain range is fixed at the size it was when you selected it, and a new item added underneath simply is not in the list.

The setting that decides whether the list is a rule or a suggestion

It lives on the Error Alert tab, which most people never open — so most drop-downs are suggestions without their author realising.

Information: Shows a message and lets the entry through. Microsoft: it “doesn’t stop people from entering data that isn’t in the drop-down list”.
Warning: The same — a message, and the value is still accepted.
Stop: “To stop people from entering data that isn’t in the drop-down list, select Stop.” This is the only one that enforces anything.
No alert at all: If the box is cleared, anything can be typed and nothing is said.

This is the one that surprises people. A drop-down looks like a constraint — a menu, with options, and nothing else available. But unless the style is set to Stop, somebody can ignore the menu entirely and type whatever they want, and the sheet accepts it. If your reason for building the list was consistent data rather than convenience, Stop is the setting you wanted and it is not the default behaviour you get by clicking through.

The reverse is also a real choice. If the list is there to save typing rather than to enforce a vocabulary — a list of common suppliers where an unusual one is legitimate — then Warning is right, and Stop would make the sheet obstructive. The point is to choose rather than to discover which one you got.

The input message is worth using. Microsoft allows a title and a message “up to 225 characters”, shown when the cell is selected. One sentence saying what the cell is for prevents most of the wrong entries before the error alert has to catch them, and it is the difference between a form people fill in correctly and one they fill in twice.

If the drop-down stops working. Three causes account for nearly all of it. The source range was a plain range and the list grew past it — fix by converting to a table. The source sheet was deleted or renamed. Or the cells were copied and pasted over, which replaces the validation rule along with the contents, because pasting carries formatting and rules with it. That last one is worth knowing when a form is used by other people: a paste can quietly remove every rule you built.

Every quoted instruction here is from Microsoft’s own Excel documentation, read on 22 August 2026. The point about pasting over validation, and the argument for choosing between Stop and Warning deliberately, are ours.

Where to start

Four ways in.

“I need the steps.”: Above — then set the alert style in making it hold
“People type things that are not on the list.”: That is the Error Alert tab — making it hold
“Data Validation is greyed out.”: Protection — go to protection and sharing
“The list stopped updating.”: Make the source a table — see the source list

Making it hold

A drop-down is a rule applied to a cell. Whether it is enforced, and whether it survives being used, are separate settings.

Locking cells in ExcelEvery cell is already locked, so the real job is unlocking the ones people should edit.Open this floor → Sorting alphabeticallyOne word in Microsoft’s first step keeps your rows together.Open this floor → Removing duplicates in ExcelThree tools and only one deletes, and what Excel actually counts as a duplicate.Open this floor →

The source list

The options live in cells elsewhere. How that range is defined decides whether the list ages well.

Merging cells in ExcelMicrosoft says merging deletes the other cells, and merged blocks break everything downstream.Open this floor → Freezing rows in ExcelOne rule from Microsoft explains every case.Open this floor → PDF to ExcelWhat survives the conversion and what arrives as text pretending to be a number.Open this floor →

A greyed-out command is usually a protected sheet rather than a fault, and Microsoft says so.

Spreadsheets as softwareThe point at which a spreadsheet has quietly become an application.Being built

BackupsThe arrangement that makes an irreversible change survivable.Being built

Large files by emailSending the workbook once it has grown.Open this floor →

Forms people fill in

A drop-down is usually part of something somebody else will complete. What makes that go well is mostly not the drop-down.

PDF formsWhen a form has to be a document rather than a sheet.Open this floor → QR codes for a Google FormThe three settings that break a printed code.Open this floor →

Customer data and the lawWhat you are holding about the people who replied, and what that obliges you to do.Being built

What this tower will not do

It will not stop at the menu path. Two settings inside it decide whether the list still works later, and both are easy to click past.

It will not let you assume a drop-down is enforced. Unless the style is Stop, anybody can type anything and the sheet will accept it.

And it will not recommend an add-in for something that is one dialog on the Data tab. What holds instead is simple: the steps, the note about tables and the alert styles are quoted from Microsoft’s own Excel documentation, with the date it was read.

Where this page got its facts

Microsoft Support — Create a drop-down list, including why the source should be a table, the Data Validation steps, the note about protected worksheets, and the Error Alert styles — support.microsoft.com, read 22 August 2026.

Written by Alberto Gulotta

Founder and editor of AI Tools Primer, writing from Palermo, Italy. Thirty-five years of taking computers apart, starting with a Commodore 64 — the long version is on the about page.

Something wrong on this page? Write to aitoolsprimer@gmail.com and it gets fixed.

Written on 22 August 2026.

Independence and limits

No affiliate links and no paid placements anywhere on this site. Nobody pays to appear here, and no company has seen this page before you did.

This is general information, not professional advice. Where a page touches money, health, safety or the law, it names its source and the date it was read — and your situation may still differ. See the privacy page and the cookie policy.