Office Settings & Configuration

How to Fix Excel Drop-Down List Rejecting Entered Values

Jason MillerJason Miller Nov 10, 2026 868 views

Question details

The user encounters an issue where a value manually typed into a cell with a drop-down list is rejected by the spreadsheet.

How to Fix an Excel Drop-Down List That Rejects Entered Values
Product
Excel
Device & OS
not provided
Scenario
Entering custom data into a cell that has a data validation drop-down menu applied.
Observed behavior
An error message displays rejecting the typed value, enforcing strict restriction to the predefined items in the source list.
Before you start

Ensure the worksheet is not protected and you have editing permissions to modify the data validation rules in the affected cells.

Solution 1Recommended

Check for Exact Matches in the Source List

Ensure the manually entered text exactly matches the formatting, spelling, and spacing of the items in the drop-down source.

A drop-down list acts as a strict data restriction by default. If a user manually types a value instead of clicking it from the list, the typed text must perfectly match one of the predefined source items. Hidden spaces, slight spelling errors, or mismatched data types (like text vs. numbers) will trigger a rejection.

1
Select the cell

Click on the cell containing the drop-down list that is rejecting your entry.

2
Open Data Validation

Navigate to the Data tab on the ribbon and click on the Data Validation button.

3
Verify the source range

In the Settings tab of the dialog box, look at the 'Source' field to see exactly where the list gets its data.

4
Inspect for discrepancies

Review the source data cells for trailing spaces, spelling variations, or formatting differences, and correct them or adjust your typed entry to match exactly.

Check for Exact Matches in the Source List
Hidden Spaces: Trailing spaces are the most common culprit. Use the TRIM function on your source data to easily remove accidental spaces.
Manage Drop-Downs Easily

Create and Manage Drop-Down Lists with WPS Spreadsheet

WPS Spreadsheet provides an intuitive interface for creating, modifying, and troubleshooting drop-down lists. You can easily adjust error alerts to prevent cell rejection, with full compatibility for Microsoft Excel data validation rules.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the restrictive drop-down list.
  2. 2. Navigate to the Data tab: Select the target cell and click the Data tab on the top menu ribbon.
  3. 3. Access validation settings: Click 'Validation' to inspect the source list or modify the data entry rules.
  4. 4. Adjust error alerts: Navigate to the Error Alert tab within the dialog and adjust the prompt to allow custom entries.
Fully compatible with Microsoft Excel (.xlsx) data validation rules and error alerts.Clear and intuitive data validation dialog box for easy troubleshooting.Lightweight software with fast processing speeds for large datasets.Free to use for basic spreadsheet management and data entry tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my drop-down list reject numbers but accept text?

This occurs if the source data cells are formatted as Text while the entered value is being recognized as a Number (or vice versa). Ensure that the cell formatting of your manual input matches the formatting of the source list cells.

How can I remove the drop-down restriction entirely?

Select the cell with the drop-down list, go to Data > Data Validation, and in the Settings tab, change the 'Allow' criteria back to 'Any value'. Click OK to remove the drop-down list and allow any input.

Can a drop-down list automatically update when I add new items?

Yes, you can make a drop-down list dynamic by formatting your source data as a Table or by using the OFFSET formula in the data validation source field. This prevents rejection when new valid items are added to the source.

What happens if the source data for my drop-down list is deleted?

If the source data is deleted, the drop-down list will appear blank when clicked. However, the data validation restriction will still apply, meaning you won't be able to enter any values until the source is restored or the validation rule is cleared.