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.

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

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

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

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

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

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.

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.

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.

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.

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

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

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.

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

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

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.

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

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.

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.

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

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

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

Result:

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

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.