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.
- Select an excel cell, then click the excel Data tab —> Data Validation item in the Data Tools group.
- Then it will open the Data Validation dialog window.
- Click the Settings tab in the dialog window, then select the item List from the Allow drop-down list.
- Then you can input the drop-down option items in the Source input text box.
- If you want to add an empty option, you can add — in the Source text box.
- Multiple drop-down list options should be separated by a comma.
- 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 - 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.
- The above method is not flexible when you want to change the drop-down options.
- Because you need to change the data validation settings when you want to change the drop-down list option.
- You can also save the drop-down options in a cell range, then reference the cell range in the data validation settings.
- 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.
- 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 - Select cell B1, and click the excel Data tab —> Data Validation item in the Data Tools group to open the Data Validation dialog window.
- In the Settings tab, select the item List from the Allow drop-down list.
- Input the cell reference formula =$A$1:$A$4Â in the Source input text box.
- Click the OK button to save the settings.
- 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.