Introduction
When using Excel, there are often times when you want to restrict the values that can be entered into a cell. “Data Validation” is the go-to feature for this.
For example, you might want to set up a list in a cell so users can choose from a dropdown, but you also want to allow them to input values not in the list.
In this article, I will explain a simple way to solve this problem.
How to Allow Values Not in the List
Normally, when you set up a list using Data Validation, Excel displays an error if you try to enter a value that isn’t in that list. Here is the procedure to prevent this error and allow free input while still providing a dropdown menu.
Steps
- Select the cell for the list First, select the cell or range of cells where you want to set the Data Validation.
- Open Data Validation settings Go to the “Data” tab on the ribbon and click “Data Validation”.
- Set up the List In the “Settings” tab, under “Allow,” select “List”. In the “Source” box, enter the range you want to use as a list (e.g.,
=A1:A10) or type the values directly separated by commas (e.g.,Apple,Banana,Orange). - Turn off the Error Message Select the “Error Alert” tab. Uncheck the box that says “Show error alert after invalid data is entered”.
- Save the settings Click the OK button to save your settings.
Result
You will now be able to select items from the dropdown list, but you will also be able to type in values that are not in the list without triggering an error message.
Important Considerations
- Risk of Typos: Since no error will be displayed, be aware that users might enter incorrect values or typos.
- Data Verification: If data consistency is critical, consider setting up a separate mechanism to verify the data (such as Conditional Formatting to highlight unique values).
Summary
The method of setting up a Data Validation list while allowing free input is a convenient technique that is surprisingly not well known. Please use it to improve your Excel workflows in business or personal projects.
