Adding A Blank Option in a Data Validation Drop Down List in Excel
Why Do You Need A Blank Value Choice?
Sometimes you may come across instances where you want to include an option for your users to select a bank/empty value in a Data Validation drop-down list. This is definitely a “nice-to-have” tweak as users can simply hit their Delete or Backspace key to clear out the cell value. However, I have found this slight enhancement can make the user experience flow a bit more nicely as there could be doubt on the user’s end if they are allowed to have a blank value.
Let’s look at two ways you can get that blank value added into your Data Validation drop-down list. We will explore
- Manually typing out the list choices
- Referencing a cell range to pull in the list choices
Typing Out The List Values
A lesser-known feature of Data Validation Lists is that you can actually hardcode the values you want to display in your list. I typically prefer this route if I don’t want to risk someone accidentally changing the list options or if the list is containing something simple like yes/no options. Instead of referencing a cell range address, you can simply type the value you want to be listed and separate them with a comma.
The oddity though is that the universal value for blank in Excel (” “) DOES NOT give us a blank result in the data validation list. For one reason or another, you have to use two dashes to achieve this effect (–).
Please note, that even though the dashes show up as the selectable option in the drop-down menu, once selected, the cell value is empty/blank. The value will also be recognized as a blank value if pointed to in formulas.
Pulling Values From A Cell Range
If you want to use a range reference to populate your Data Validation List, you simply need to ensure the first row in your reference does not have a cell value. This will carry through to what is displayed in your drop-down menu.
I Hope This Helped!
Hopefully, I was able to explain how you can achieve the effect of having a no-value/empty option for your users to select in Data Validation drop-down lists. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![4 PRO Ways to Create A Filtering Search Box [Excel Download]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/ExcelFilteringSearchBox.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.