How To Add A Blank Option In A Drop Down List In Excel

This article will tell you how to add an empty option in the excel data validation drop-down list. There are 2 methods to implement this, I will show them with examples.

1. How To Add Empty Option In Excel Data Validation Drop-Down List Examples.

1.1 Add The Empty Option By Hard Coding.

1. Select an excel cell, then click the excel Data tab —> Data Validation item in the Data Tools group.
2. Then it will open the Data Validation dialog window.
3. Click the Settings tab in the dialog window, then select the item List from the Allow drop-down list.
4. Then you can input the drop-down option items in the Source input text box.
5. If you want to add an empty option, you can add — in the Source text box.
6. Multiple drop-down list options should be separated by a comma.
7. For example, if you input the text –,Excel,Word,PPT in the Source text box, it will show the below drop-down list in the cell.
   

   ⇓

   —

   Excel

   Word

   PPT

8. When you select the — from the drop-down list, it will show an empty option value in the cell.

1.2 Add The Empty Option From Reference To A Cell Range.

1. The above method is not flexible when you want to change the drop-down options.
2. Because you need to change the data validation settings when you want to change the drop-down list option.
3. You can also save the drop-down options in a cell range, then reference the cell range in the data validation settings.
4. And you can add an empty cell in the referenced cell range, and the empty cell will be mapped to the empty option in the drop-down list.
5. Add the below data in cell range A1:A4, please note cell A1‘s value is empty.
   

   	A	B
   1
   2	Word
   3	PPT
   4	Excel

6. Select cell B1, and click the excel Data tab —> Data Validation item in the Data Tools group to open the Data Validation dialog window.
7. In the Settings tab, select the item List from the Allow drop-down list.
8. Input the cell reference formula =$A$1:$A$4 in the Source input text box.
9. Click the OK button to save the settings.
10. Now you can find there is a drop-down arrow beside cell B1, when you click the arrow you can see the first drop-down option is empty.