Excel Data Validation Lists: Allow Custom Input?

Yes. In Excel for Microsoft 365 on Windows, a data validation list can keep its dropdown while allowing entries that are not on the list: select the cells, open Data > Data Validation, and clear the Show error alert after invalid data is entered option on the Error Alert tab. Alternatively, choose Warning or Information to let users override a warning when they type a non-listed value.

What a validation list does

A list validation rule gives a cell a set of suggested or permitted entries. When the cell is selected, Excel can show a dropdown containing those entries. The dropdown itself is a way to pick a value; it does not, by itself, decide whether a person can type something else. That decision is controlled by the rule’s error-alert settings.

With the standard Stop alert enabled, Excel rejects a value that does not match the list when someone types it into a validated cell. With the alert switched off, Excel still displays the dropdown, but users can type other values without being stopped. With a Warning or Information alert, Excel flags the mismatch and gives the user a way to keep the entry or return to editing it.

This distinction is useful when a list represents common choices rather than every possible answer. For example, a support log might offer Billing, Delivery, and Returns in a dropdown, while still allowing an employee to type Account access for an unusual case. The list remains available for routine entries, but it no longer acts as a strict gate.

Allow typing without a blocking alert

To allow any typed value while retaining the dropdown in Excel for Microsoft 365 on Windows:

Select the cell or range that has the list validation. To apply the setting to a range, select the whole range first.
Choose Data > Data Validation. If the command is unavailable, check whether the worksheet is protected or whether the selected cells are in a context that prevents changes.
On the Settings tab, confirm that Allow is set to List. Check In-cell dropdown if you want the arrow to remain visible.
Open the Error Alert tab and clear Show error alert after invalid data is entered.
Choose OK, then test both a listed item and a value that is not on the list.

Clearing the alert does not erase the list source or remove the dropdown. It changes the response to an invalid entry: Excel no longer displays the blocking alert. If you want users to see a prompt before they keep an unusual value, leave the alert enabled and select Warning or Information for Style instead of Stop.

In Excel for Mac, the equivalent command is also available from Data > Data Validation, though the dialog layout can vary by release. Excel for the web can be used to work with validation dropdowns; if a workbook depends on a particular alert or override behavior, test entry in the browser your team uses as well as in desktop Excel.

Stop, Warning, Information, and no alert

The Error Alert tab determines what happens after a user enters a value that does not satisfy the rule. The choices suit different levels of control:

Stop: The user cannot keep the invalid value through ordinary typing in the cell. This is appropriate when entries must come from an approved set, such as a controlled status field.
Warning: Excel displays a warning but provides a way to accept the value anyway. It is useful when exceptions are legitimate but should be noticed.
Information: Excel informs the user that the value is not on the list but still allows an override. This is a lighter reminder than a strict restriction.
Clear Show error alert: Excel does not show the invalid-entry alert. Users can type freely, while the dropdown remains a convenient reference.

The alert title and message can be customized when the alert is enabled. A useful message explains the rule and what to do next, such as asking users to choose a listed department or contact the workbook owner to request a new one. A vague message such as Invalid entry gives less help. An alert is guidance, however, not a substitute for checking important data later.

Keep the dropdown and maintain its choices

On the Settings tab, the Source box defines the list. It can contain items typed directly into the box, separated by the list separator used by the computer, or refer to cells containing the choices. A cell range is generally easier to maintain when the choices may change or when several validation cells need the same list. The In-cell dropdown setting controls whether Excel shows the selector; it is separate from the error-alert setting.

For example, a project tracker might use a source range containing Not started, In progress, and Complete. Keeping those values in worksheet cells makes it easier to review and update the choices than editing a long typed source string. If the source is on a different worksheet, a named range is a common way to refer to it in data validation. After changing the source, test the dropdown and the behavior for a value that is not included.

Do not confuse Ignore blank with permission to type custom values. Ignore blank affects how blank cells are treated by the validation rule; it does not turn off the invalid-entry alert for other values. Similarly, hiding the dropdown arrow does not make the list unrestricted. The alert setting and the list source do different jobs.

When custom input is useful

Allowing custom input works well when a list is a set of suggestions, not a complete catalog. A team may standardize frequent customer requests while permitting rare cases. A survey worksheet might offer common response categories but accept an Other response. A project status list might work as a starting point during planning, when teams have not yet agreed on every status.

For a more structured Other response, consider adding an explicit Other item to the list and providing a neighboring cell for the explanation. This preserves a consistent category for summaries while still capturing detail. If every person types a different phrase for the same concept, later sorting and reporting can become difficult: Delivery issue, Late delivery, and Package arrived late may describe the same problem but appear as separate values.

Where consistent reporting matters, use a Warning alert or keep Stop enabled and provide a documented way to request new list items. Where flexibility matters more than standardization, disable the alert, but explain which entries are useful and how people should spell or format them.

What validation does not guarantee

Data validation is a data-entry aid, not a security boundary or an absolute guarantee that every cell contains an approved value. Users may paste or fill values into cells in ways that do not behave like ordinary typing through the validation prompt. Existing values can also remain after someone changes the source list or rule. If the data is important, inspect it after collection rather than assuming the validation rule caught every exception.

Excel includes a Circle Invalid Data command in the Data Validation menu that can help identify cells whose current contents do not satisfy their rules. It is useful after changing a source list or reviewing a workbook, but it does not repair the values automatically. For a workbook used by many people, protect the sheet against accidental changes to the validation setup while leaving the intended input cells unlocked. Protection helps guard the layout and rules against casual edits; it still should not be treated as a security control for sensitive information.

Common problems and checks

The dropdown disappeared: Reopen Data > Data Validation and check that Allow is List and In-cell dropdown is selected. Clearing the error alert should not remove the arrow.
Excel still blocks custom typing: Check that Show error alert after invalid data is entered is actually cleared for the selected cells. If it remains enabled, change Style to Warning or Information to permit an override, or clear the setting for unrestricted typing.
Only some cells behave differently: The selected cells may have different validation rules. Inspect each affected cell, or select the intended range and apply one consistent rule.
A typed value appears accepted, but a pasted value causes confusion: Validation prompts are designed around data entry, and paste operations may not trigger the same check. Review pasted data separately.
The list seems out of date: Check the cells or named range used as Source. A dropdown reflects its source; changing the alert does not update the choices.

When troubleshooting, test in a spare cell or a copy of the workbook so you can distinguish a rule problem from a source-range problem. Check one value that is on the list, one that is not, and a blank if blanks matter to the workflow. Also check the behavior in the Excel version and platform where the people entering data will actually work.

Frequently Asked Questions

Can I keep the dropdown but let users type anything?
Yes. Keep Allow set to List and In-cell dropdown selected, then clear Show error alert after invalid data is entered. The dropdown stays available, but Excel does not block values outside the list.

Can I let users type custom values but show a warning?
Yes. Leave the error alert enabled and choose Warning or Information as the Style. These alert types notify users about an entry that is not on the list while allowing them to override the rule.

Does Ignore blank allow values that are not in the list?
No. Ignore blank concerns blank cells. To allow other typed values, change the Error Alert behavior; to allow only approved values, keep Stop enabled.

Why can a value outside my list still appear in a cell?
The alert may be disabled or set to Warning or Information, or the value may have been pasted or filled rather than entered normally. The rule may also have been changed after the value was entered. Review the cell’s validation settings and use Circle Invalid Data to help check existing entries.

Can I make Excel accept only list values except for one custom value?
A standard list rule cannot express a simple exception while also accepting arbitrary text. Add the exception to the source list if it is an approved choice. If the exception needs extra explanation, pair an Other choice with a separate detail cell.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.