Excel Drop-Down Lists: Set Up Validation and Check Pasted Data

Software & Apps / Excel data entry

Create an Excel drop-down list with Data Validation, set its allowed values, and use a Stop error alert when typed entries must match the list. Then check existing records, pasted data, and newly added rows. A visible drop-down arrow is an entry aid; it does not prove that every value in the column is valid.

This guide uses a hypothetical project tracker to show the complete workflow: define statuses, apply the rule, explain it to users, maintain the choices, and review the workbook before reporting from it. The main instructions use desktop Excel for Windows; check the corresponding controls in your Mac or web edition.

Define choices that people can use consistently

Before opening a dialog, decide what the column means. “Status” might describe delivery progress, payment, or approval. Those are different questions; putting all three into one list produces records that are difficult to interpret.

For this hypothetical tracker, column D records delivery progress. The permitted choices are:

Keep the explanation in the workbook so “Complete” does not become one person’s “submitted” and another person’s “approved.” Use a separate notes column for detail, rather than adding a new status every time an unusual situation appears.

Also decide what an empty status means. If it indicates an unfinished record, review it as missing information. If it is genuinely optional, say so. A blank cell should not silently stand for several different business states.

Create the drop-down from a visible source range

Work on a copy while configuring the tracker. In this example, put the heading Allowed status in H1 and the four choices in H2:H5. Keep that list separate from the records people will enter.

Following Microsoft’s drop-down creation instructions:

1. Select the destination cells D2:D101, excluding the Status heading.
2. Open Data → Data Validation.
3. On Settings, choose List under Allow.
4. Set Source to =$H$2:$H$5, or select those four source cells. Exclude H1.
5. Enable In-cell dropdown and choose the intended Ignore blank setting.
6. Confirm the dialog, select a destination cell, and inspect the available choices.

The dollar signs keep the illustrated source reference fixed. D2:D101 is the entry area; H2:H5 is the vocabulary. Maintaining one does not automatically establish that the other covers every future record.

Microsoft also documents typing a short comma-separated list directly into Source. A cell-based list is usually easier for a team to inspect and discuss. For changing vocabularies, consider a table-backed source; our Excel Table versus range guide explains that structure separately.

Use an input message and the right error alert

An instruction helps before an error occurs. Microsoft’s validation guidance describes an Input Message shown when a cell is selected and an Error Alert shown for an invalid entry. Configure both around the user’s next action.

For the hypothetical tracker, an input message could say: “Choose delivery progress. Use Blocked when a dependency prevents work; explain it in Notes.” An error message could say: “Choose one of the listed statuses. Put additional detail in Notes.”

Keep Show error alert after invalid data is entered enabled. Microsoft’s advanced validation guidance distinguishes the alert styles:

A Stop alert still does not establish that an entire record is complete or that an entered status is truthful. Someone can choose Complete for unfinished work. Define the review responsibility separately from the spelling restriction.

Copying can introduce invalid values or replace the rule

There are two problems to distinguish. First, an invalid value can enter a cell that still has validation. Microsoft explains that validation messages are designed for direct typing; copying, filling, formulas, or macros can introduce values without the same alert behavior in its invalid-data guidance.

Second, the destination’s validation itself can change. Microsoft’s paste options reference says ordinary paste includes source data, formatting, formulas, and validation, among other elements. It separately documents Values and Validation paste choices.

For a controlled import, prefer an explicit values-only paste when you intend to retain the destination’s rules and formatting. That is not a promise that the pasted values meet the rule. Inspect a pasted cell’s validation settings and review its value afterward.

Use a disposable workbook copy to demonstrate the team’s actual paste method. Include an intentionally invalid value such as “Nearly done” and a source cell with different validation. Record what happens in the Excel edition people really use. This is a suggested test, not a claim of testing performed for this article.

Review records that already exist

Adding a rule does not clean historical records. Microsoft’s advanced validation guidance notes that existing invalid entries are not automatically announced when validation is applied. Schedule a separate cleanup before using the column in a report.

In desktop Excel editions that provide it, select Data → Data Validation → Circle Invalid Data. Microsoft documents this as a way to mark values that do not meet their validation criteria. The feature may not appear in Excel for the web.

Use the findings as a review queue. For each unfamiliar value, decide whether it is a typo, an old category, or a genuine exception. Do not replace everything unusual with Complete merely to remove the circles. If clearing the circles, remember that removing a visual marker is not a correction.

Check rule coverage separately. An invalid-data check based on validation criteria cannot stand in for confirming that every intended cell has the intended rule. Sample existing rows, newly appended rows, and areas touched by imports.

Update choices and destination coverage deliberately

Microsoft’s list maintenance guide distinguishes manually entered lists, cell ranges, named ranges, and table sources. For a fixed source, adding a fifth value in H6 requires including it in the referenced range. For a properly table-backed source, table growth supports automatic list updates.

Check both directions after changing the vocabulary: the new choice appears where expected, and retired choices are handled in existing records. Renaming an allowed choice is also a reporting decision. Decide whether historical records retain the old label or need an approved migration.

Microsoft documents updating other cells with the same settings from the validation dialog. Use that option only after confirming which cells share the rule. Keep unrelated lists, such as payment state and delivery progress, independent.

Have one named owner approve vocabulary changes. Keep a short note of what changed, why, and how historical rows were treated. This is especially useful when a report compares results across months.

Run this check before the tracker becomes routine

Use a copy with fictional records. A successful selection is the starting point, not the complete test.

• Select every allowed status and confirm that the text is readable at the chosen column width.
• Type an invalid status and verify the expected Stop alert.
• Test an empty cell and a cleared entry against your documented completeness requirement.
• Paste valid and invalid values using the team’s normal import procedure.
• Inspect whether the intended rule remains after that paste.
• Add a new record beyond the initial destination area and check its validation.
• Change a source choice in the copy and inspect the affected lists and historical values.
• Reopen the workbook in each edition used by the team and repeat the critical checks.

If a menu is unavailable, first finish any active cell edit and check with the workbook owner about protection or sharing restrictions. Avoid removing protection from a live shared tracker just to experiment. Arrange changes in an authorized working copy and verify the result before replacing the team’s version.

For bulk intake or high-consequence decisions, decide whether a spreadsheet is the right entry system. An application can enforce rules at its submission boundary; an Excel drop-down should remain one part of a documented review process.

Frequently asked questions