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

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.

How to Add Blank Option to Excel Drop Down List: 2 Methods

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.

Steps:

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

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

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

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.

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

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.

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.

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.

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.

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.

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

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.

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

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

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.

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’.

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.

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

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’.

Steps:

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

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.

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)

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.

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.

Download Practice Workbook

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

Add Blank Option to Drop Down List.xlsx

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.

<< Go Back to Excel Drop-Down List | Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

How to Add Blank Option to Excel Drop Down List: 2 Methods

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

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

Create Drop Down List without Blank Option in Excel

1. Using Excel Table

2. Using Named Range

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

Conclusion

Related Articles

Leave a reply Cancel reply