Why an Excel drop-down can still contain invalid values
Excel refuses a status that isn’t on the list when you type it, yet that status appears after a colleague pastes in a batch of rows. How did it get past the drop-down?
Check whether the pasted cell still has the intended rule
Open Data → Data Validation on the affected cell. If its List rule and source are intact, investigate the value it contains. If the rule has disappeared or points to another list, restore it before auditing the entries.
This is a longstanding problem. In a 2013 Microsoft community question, a user reported that typing an invalid entry was blocked but copying another cell into the range was not. The opening paragraph of Microsoft’s validation-circle guide explains that copied or filled data can contain invalid values without triggering the messages used for direct entry.
Imagine a team report expecting Pending, In progress, or Complete. Another team sends rows labeled Finished. A pasted row can contain that different wording even though a direct typing test rejected it. A report counting only Complete would then miss work that someone intended to mark as completed.
| What you find | First action | What remains |
|---|---|---|
| Correct rule, invalid value | Compare the value with the allowed choices | Confirm its meaning and correct it |
| Missing or changed rule | Restore the intended validation | Audit values already in the cells |
| A legitimate new status | Decide whether to extend the list | Update the checking range as well |
The menu steps below use Excel for Microsoft 365 or Excel 2024 on Windows. Excel for the web can create drop-downs, but the validation-circle audit described below is a desktop feature.
Use a small list to establish the intended behavior
In a practice copy, put Allowed status in H1 and enter Pending, In progress, and Complete in H2:H4. Put Status in D1 and reserve D2:D100 for entries. This separates the vocabulary from the work records that use it.
- Select D2:D100 and open Data → Data Validation.
- Choose List under Allow and set Source to
=$H$2:$H$4. - Enable In-cell dropdown. Leave the H1 heading out of the source.
- On Error Alert, enable the alert and select the Stop style.
- Try a valid choice, then directly type Finished in another practice cell.
These controls follow Microsoft’s drop-down instructions. Warning and Information allow the user to proceed with an invalid entry; Stop blocks that direct entry. The visible arrow and the error behavior are separate settings.
Use an input message to explain where extra detail belongs. For example, ask colleagues to choose In progress and put “waiting for the customer” in a comments column. A status containing both the category and a free-form explanation will not match the short category your report expects.
Also decide what an empty cell means. An unfinished draft might legitimately have blanks, while a submitted record needs a status. The checking column below reports Missing separately from Pending, rather than treating those situations as equivalent.
Paste the rule without copying a neighboring task’s status
Microsoft’s paste-options reference says a normal Ctrl+V operation transfers cell attributes including validation. Moving an entire block from another workbook can therefore change the receiving form as well as its data.
Suppose D2 still uses the correct H2:H4 list, but D20:D30 have lost it. Copy D2, select D20:D30, and choose Home → Paste → Paste Special → Validation. This transfers the rule instead of repeating D2’s status across the selected tasks.
Inspect the source in the repaired cells and check that their previous values remain. The dollar signs in this example keep the allowed-list reference fixed. Do not spread one rule across cells that deliberately use different lists, such as departments with distinct workflow stages.
For later imports, Values pasting avoids bringing the source cells’ validation and formatting along with the data. There is a narrower detail in Microsoft’s developer documentation: it describes validation being triggered when Values are pasted from elsewhere in the same workbook. That does not establish the behavior of every external source or paste method. Test the route your colleagues actually use and audit the resulting entries.
Find existing mistakes after restoring the rules
Restoring a list does not rewrite Finished as Complete. In desktop Excel, open the arrow beside Data Validation and choose Circle Invalid Data. Microsoft’s validation-circle guide explains that this checks existing values against their rules, including values introduced by copying or calculated by formulas.
If D20 has lost its rule, restore that rule before running the circle check. An unmarked cell may simply have no criteria to test against. Clear Validation Circles only hides the indicators; correcting an invalid value removes its circle because the value now satisfies the rule.
For a larger intake, add an independent checking column. Put Status check in E1 and the following formula in E2, then copy it through E100. This example assumes D contains text statuses or blanks, and H2:H4 contains the three allowed choices.
=IF(D2="","Missing",IF(SUMPRODUCT(--(D2=$H$2:$H$4))>0,"OK","Outside list"))The SUMPRODUCT expression compares D2 with each allowed value. The double minus turns the comparison results into ones and zeros to sum. IF handles a blank first, then reports whether any comparison matched. The formula uses the list directly; it does not depend on D2 retaining its drop-down rule.
| Example input | Result | Follow-up |
|---|---|---|
| In progress | OK | Matches an allowed status |
| Finished | Outside list | Ask what was completed |
| In progress with a trailing space | Outside list | Inspect and remove unintended spacing |
| Empty cell | Missing | Request a status for a submitted row |
OK means the label matches, not that the work is complete. Finished might refer to a review stage rather than the whole task. Confirm its meaning before replacing it. Keep the received data on a separate sheet if you need to compare an original label with the final status.
Preserve column E when importing: overwriting the formulas also removes this check. An Excel error such as #N/A in column D can propagate into the formula, so resolve source errors separately.
Extend the list and its audit together
If you add On hold in H5, the old source H2:H4 will still exclude it. Microsoft’s list-maintenance instructions describe updating a range-based source. Change this example to H2:H5 in both validation and the checking formulas. Otherwise, a newly available choice can still be flagged by the old audit range.
For frequently changing choices, an Excel table can serve as the list source. Drop-downs based on that table update as items are added or removed. This expands the choices; it does not promise that every new work-record row beyond D100 has acquired your intended validation and checking formula.
Removing a choice also affects existing records. If In progress becomes Under review, rows containing the old wording no longer match the new list. Decide whether the change is only a rename or a different business stage before updating those rows.
Rehearse the import your team will perform
For repeated bulk imports, receive the original data in a staging sheet, inspect Missing and Outside list results, and transfer the agreed values to the final input area. Keep the original labels alongside the corrected ones until the reporting owner has resolved their meaning.
To reduce accidental edits to the list and checking formulas, use worksheet protection. Unlock the intended input cells through Format Cells → Protection before enabling Review → Protect Sheet. Leave the list and formula cells locked, then check that a colleague can still enter a status. Those input cells remain editable, so review the checking column for missing or unrecognized values before using the submitted rows.
In a disposable copy, test direct typing, ordinary paste, and Values paste from the actual source. After each, inspect the rule and audit the contents. If protection disables the validation controls, the workbook owner can unprotect the sheet to inspect and repair the rule, then protect it again. Include the final row of the intended input area and one newly added status. Those tests expose a missing rule, an outdated source, and a checking formula that stops before the latest records.
Remove the practice entries while keeping a known-good validated cell available for repairs. Hand over the locations of the allowed list and checking range together, so the next person adding a status can update both.