How to Fix Excel Drop-Down List Rejecting Entered Values
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.

- 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.
Ensure the worksheet is not protected and you have editing permissions to modify the data validation rules in the affected cells.
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.
Click on the cell containing the drop-down list that is rejecting your entry.
Navigate to the Data tab on the ribbon and click on the Data Validation button.
In the Settings tab of the dialog box, look at the 'Source' field to see exactly where the list gets its data.
Review the source data cells for trailing spaces, spelling variations, or formatting differences, and correct them or adjust your typed entry to match exactly.

Change the Error Alert to Allow Custom Entries
If users legitimately need to enter values that are not part of the predefined drop-down list, adjust the error alert settings.
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. Open your workbook: Launch WPS Spreadsheet and open the file containing the restrictive drop-down list.
- 2. Navigate to the Data tab: Select the target cell and click the Data tab on the top menu ribbon.
- 3. Access validation settings: Click 'Validation' to inspect the source list or modify the data entry rules.
- 4. Adjust error alerts: Navigate to the Error Alert tab within the dialog and adjust the prompt to allow custom entries.

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.




