How to Add a Blank Option to a Drop-Down Lists and Create a Drop-Down List Without a Blank Options

METHODS TO ADD

Method 1 – Use an Empty Cell as Reference

  • Insert a Blank Cell:
    • Add an empty cell at the beginning of your source list. For example, let’s say you have a dataset containing various fruit names, and you want to create a drop-down list based on this data.

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

    • Make sure that Cell B5 (or any other empty cell) is left blank.

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

  • Create the Drop-Down List:
    • Position your cursor in Cell D5 (or any other desired cell where you want the drop-down list).

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

    • Navigate to the Excel Ribbon, go to Data, select Data Tools, choose Data Validation and click on Data Validation.

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

    • The Data Validation dialog will appear.
  • Configure the Settings:
    • In the Settings tab, select List from the Allow section.
    • Specify the source list (which includes the fruit names along with the blank cell).
    • Make sure to uncheck the Ignore Blank option.
    • Click OK to create the drop-down list.

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

  • As a result, you’ll have a drop-down list that includes the blank option.

Read More: How to Create Drop Down List in Multiple Columns in Excel


Method 2 – Manually Type List Values

  • Position the Cursor:
    • Place your cursor where you want to create the drop-down list.
  • Access Data Validation:
    • Navigate to Data, select Data Tools, choose Data Validation and click on Data Validation. This will open the Data Validation dialog.
    • 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.

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

  • Configure the Settings:
    • In the Settings tab, select List from the Allow field.
    • In the Source field, type a double dash (- -) at the beginning of each item you want to display in the drop-down list.
    • Click OK to confirm.

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

As a result, you’ll have a drop-down list that includes the double dash (- -) as an option. However, when you select it, the cell will display a blank value.

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

Read More: Create a Searchable Drop Down List in Excel


METHODS TO CREATE

Method 1 – Using an Excel Table

  • Convert the Source Data to an Excel Table:
    • Before creating the drop-down list, convert your original source data range into an Excel table. This step is essential because it ensures that your drop-down list remains dynamic even if you add or remove items from the source table.

Create Drop Down List without Blank Option in Excel

    • To create an Excel table, select the data range (e.g., the list of fruits) and press Ctrl + T.
    • Let’s convert the original data range to an Excel Table using Ctrl + T.

Using Excel Table

    • Use the Name Box (located near the formula bar) to name the table. For example, you can name it Table1.

Using Excel Table

  • Create the Drop-Down List:
    • Choose the cell where you want to place the drop-down list (e.g., Cell D5).
    • Go to the Data tab and click on Data Validation.
    • In the Data Validation dialog, select List under the Allow section.
    • In the Source field, enter the formula =$Table1. Note that you should include the dollar sign ($) before the cell reference of the source data.
    • Click OK to create the drop-down list.

Using Excel Table

  • As a result, you’ll have a drop-down list without any blank options.

Using Excel Table

  • If you later add new items to the source table, the drop-down list will automatically update accordingly.

Read More: Create Excel Drop Down List from Table


Method 2 – Using a Named Range

  • Define a Named Range:
    • Select the entire data range (B5:B14) that you want to use for your drop-down list.
    • Go to Formulas, select Defined Names, choose Define Name and click on Define Name.

Using Named Range

    • In the New Name window, enter a descriptive name (e.g., “Fruits”) in the Name field.
    • Check the Refers to box and press OK to complete the naming process.

Using Named Range

  • Create the Drop-Down List:
    • Choose the cell where you want to place the drop-down list (e.g., Cell D5).
    • Go to the Data tab and click on Data Validation.
    • In the Data Validation dialog, select List under the Allow section.
    • In the Source field, enter =Fruits (the name you defined earlier) and press OK.

Using Named Range

  • As a result, we will create the below drop-down list without a blank option.

  • Removing Blank Options:
    • Suppose you already have a drop-down list (created from a named range called “FruitList”) that includes blank options.

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

    • Go to Formulas and select Name Manager (from the Defined Names group).

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

    • In the Name Manager dialog, select your range (e.g., “FruitList”) and click Edit.

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

    • In the Edit Name dialog, enter the following formula in the Refers to field:
=OFFSET(FIx!$B$5,0,0,COUNTA(FIx!$B:B)-2,1)
    • Press OK to confirm.

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

Result:

  • The drop-down list will now display all the fruits from the named range, excluding any blank options.

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

 

How Does the Formula Work?

  • COUNTA(FIx!$B:B)-2
    • The COUNTA function counts the number of non-empty cells in column B of the sheet named “FIx.”
    • In this case, it returns a value of 10 because there are 10 non-empty cells in that column.
    • We subtract 2 from this count because there are two cells in the range that are not fruits (presumably headers or other non-fruit data).
  • OFFSET(FIx!$B$5,0,0,COUNTA(FIx!$B:B)-2,1)
    • The OFFSET function returns a reference to a range based on a given reference cell (in this case, Cell B5 on the “FIx” sheet).
    • The parameters are as follows:
    • Starting reference: Cell B5
    • Rows offset: 0 (no vertical offset)
    • Columns offset: 0 (no horizontal offset)
    • Height: COUNTA(FIx!$B:B) – 2 rows (excluding the non-fruit cells)
    • Width: 1 column (since we want a single-column range)
  • The resulting range includes the fruit names without any blank options.

Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo