How to Add Blank Option to Drop Down List in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you may want to include a blank option in the drop-down list in excel. In Microsoft Excel, there are some easy methods available to add empty cells in Data Validation drop-down list. In this article, I will discuss how you can create a drop-down list with or without blank options. Besides, I will show the deletion of blank options from a drop-down list.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


2 Methods to Add Blank Option to Excel Drop Down List

1. Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

In this method, I will add a blank option by referencing an empty cell. Let’s assume I have a dataset containing several fruit names. Now, I will create a drop-down list based on this list, along with the blank options.

Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

Steps:

  • First, insert a blank cell at the beginning of the source list. For instance, I have made Cell B5 empty.

Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

  • Now, put the cursor in Cell D5 to create a drop-down list there.

Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

  • Then, from Excel Ribbon, go to Data > Data Tools > Data Validation > Data Validation.

Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

  • As a result, the Data Validation dialog appears. Under the Settings tab, choose the List option from the Allow section, specify the Source list, and press OK. Remember, you have to uncheck the Ignore Blank option too.

Use Empty Cell as Reference to Add Blank Option in Excel Drop Down List

  • Upon pressing OK, the below drop-down list is created along with a blank option.

Related Content: Create Excel Filter Using Drop-Down List Based on Cell Value


2. Manually Type List Values to Add Blank Option in Excel Drop Down List

While creating a drop-down list, you can enter source data manually in the Data Validation dialog. To perform the task follow the below steps.

Steps:

  • Put the cursor where you want to locate the drop-down list. Then go to Data > Data Tools > Data Validation > Data Validation to bring the Data Validation dialog.

Manually Type List Values to Add Blank Option in Excel Drop Down List

  • Then, choose List from Allow field. In the Source field, type Double Dash () at the beginning of other items you want to display in the drop-down. After that, press OK.

Manually Type List Values to Add Blank Option in Excel Drop Down List

  • Consequently, here is the drop-down list we will get as output. Even though the list displays Double Dashe () as an option, upon selection, it will show blank values.

Manually Type List Values to Add Blank Option in Excel Drop Down List

Read More: Excel Drop Down List Not Working (8 Issues and Solutions)


Create Drop Down List without Blank Option in Excel

In this section, I will show you how to create Data Validation drop-down list without a blank option. Let’s consider I have a long list of fruits as below. From this list, I will create a drop-down list without blank options.

Create Drop Down List without Blank Option in Excel

1. Using Excel Table

Before creating the drop-down list, you can convert the source data list to an excel table. The main advantage of creating an excel table is, even if you add extra items to the source table, drop-down data too updated accordingly without any further steps.

Steps:

  • First, I will convert the original data range to an excel table using Ctrl + T.

Using Excel Table

  • Then, using the Name Box, name the table as you want. For instance, my table has the name: Table1.

Using Excel Table

  • After that, I will create a drop-down list in Cell D5 by following the path Data > Data Tools > Data Validation > Data Validation. Afterward, in the Data Validation dialog, specify Source data from Table1 (see screenshot). Remember, you put the $ symbol before the cell reference of the source data. Finally, press OK.

Using Excel Table

  • Once you click OK, the below drop-down list is created without any blank option.

Using Excel Table

  • Later, if you add a new item to the source table, automatically the drop-down list will be updated accordingly.

Read More: Create Excel Drop Down List from Table (5 Examples)


Similar Readings


2. Using Named Range

You can generate a drop-down list using the named range in excel. To do that, I will give a name to my data range using the Define Names option.

Steps:

  • First, select the entire data range (B5:B14). Then go to Formulas > Defined Names > Define Name > Define Name.

Using Named Range

  • As a result, the New Name window will appear, enter a name in the Name field, check Refers to and press OK to complete the naming process. I have named my data range ‘Fruits’.

Using Named Range

  • Now, I will create a drop-down list in Cell D5. To do that go to Data > Data Tools > Data Validation > Data Validation. When the Data Validation window appears, enter ‘=Fruits’ in the Source field and press OK.

Using Named Range

  • In the end, we will get the below drop-down list without a blank option.

Read More: How to Create List from Range in Excel (3 Methods)


Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

Till now, I have discussed creating a drop-down list with or without blank options. However, in this section, I will show you how to delete blank options from a previously created drop-down list. Here, we have a drop-down list that has several blank options. Let’s assume we created this drop-down list from a named Range called ‘FruitList’.

Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

Steps:

  • Firstly, go to Formulas > Name Manager (from the Defined Names group).

Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

  • As a consequence, the Name Manager dialog will show up. Choose your range and press Edit. I will work on the range FruitList, so I have selected that.

Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

  • Consequently, the Edit Name dialog appears. Type the below formula in the Refers to field and press OK.
=OFFSET(FIx!$B$5,0,0,COUNTA(FIx!$B:B)-2,1)

Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

  • After pressing OK, you will be brought back to the Name Manager dialog. Now, if you put the cursor on the Refers to the field, you will see that all the fruits of the named range, excluding the blanks, are outlined (see screenshot). Press Close to end the process.

Fix Blank Option in Drop Down List Using Dynamic Named Range with Excel Formula

  • Finally, now, if you expand the drop-down list the below output is displayed. All the blank options are removed from the drop-down list.

🔎 How Does the Formula Work?

  • COUNTA(FIx!$B:B)-2

This part of the formula returns:

{10}

Here the COUNTA Function returns the number of cells in column B that are not empty. We have subtracted 2 from the COUNTA formula as we have data in two cells that are not fruit.

  • OFFSET(FIx!$B$5,0,0,COUNTA(FIx!$B:B)-2,1)

This part returns:

{“Watermelon”;”Apple”;”Orange”;”Grapes”;”Cherries”;”Banana”;”Kiwi”;”Grapefruit”;”Mandarin”;”Coconut”}

Here the OFFSET Function returns a reference to a range that is a given number of rows and columns from a given reference.

Read More: How to Create Dynamic Dependent Drop Down List in Excel


Conclusion

In the above article, I have tried to discuss several methods to add a blank option to a drop-down list in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo