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

We’ll provide you with 8 solutions in case your drop down list function is not working. Let’s consider the following dataset where some items are given with their order ID, states of U.S., and sales.

We have created a drop-down list for “Items”:

Problem & Solution 1 – When Drop Down List is not Visible

1.1. If Objects are Hidden

Look at the following figure where the drop down list is not visible.

To find the reason why, click on the File tab> Option.

Excel Options will appear. Click on Advanced.

As shown above, the Nothing (hide objects) option is checked which is why the list drop down list is not visible.

Uncheck that option and check the All option instead:

You’ll get the following output where the drop down list for “Items” is visible.

1.2. Checking the Drop Down Option

No we look at another reason why the Drop Down Option is not appearing.

Here, the box before the In-cell dropdown is unchecked. That’s why the arrow is not visible.

If you want to fix the problem, check the box before the In-cell dropdown option. And you’ll see the following output.

1.3. Highlighting The Cell Using the Combo Box

Steps:

⏩ Select the Developer tab > Insert ribbon > Combo Box (Form Control).

⏩ Draw a box in the following way.

⏩ Right-click on the box and pick the Format Control option.

⏩ Then fix the input range as $C$5:$C13 and the cell link as H5.

⏩ Enter OK.

Now if you click on the drop-down arrow, you’ll see the list as shown in the following figure.

1.4. If Workbook is Corrupted

To rectify the problem select the file that you want to open and then choose the Open and Repair option by right-clicking on the Open option.

Problem & Solution 2 – Showing Blank in Drop Down List

If you open the Data Validation dialog box by clicking the Data tab > Data Validation option from the Data Tools ribbon, you’ll see the source.

The source in this instance is $B$5$:B$19. This means there are some blank cells in the source that create blank space.

Remove the blank cells as follows:

You’ll now get the following output without blank cells.

Problem & Solution 3 – Problem with New Entry (Auto-Updating of Drop Down List)

In the following list of “Items” a new entry is added, namely “RAM”, but it is not found in the drop-down list.

We can create a dynamic range by utilizing the OFFSET function as follows:

Input the formula below in the Source option:

=OFFSET($B$5,0,0,COUNTA(B:B)-1)

Here, B5 is starting the cell of “Items” and B:B is the column of “Items”.

The drop down menu will now include the new entry “RAM”.

Problem & Solution 4 – If Valid Entries Are Not Allowed and Drop Down List Not Working

As shown below we’re now dealling with a delimited list where three types of cells are available in the drop-down list.

Whenever you type “no” instead of “No”, you’ll get a default message:

This message clarifies that the entered value doesn’t match with the delimited list.

As you can see, the drop-down list is case-sensitive so make sure to enter the exact values with upper and lower case as needed.

Problem & Solution 5 – If Invalid Entries are Allowed and Drop Down List Not Working

In the following example, you can see that an item, namely “Smart Phone”, is allowed as an entry but that it is not available in the “Items” drop down menu and no error message is shown by default.

This is because there is a blank cell in the lower part of the “Items” list.

You can solve the issue by unchecking the box before the Ignore blank option in the Data Validation dialog box and pressing OK.

If you now enter “Smart Phone”, the invalid entries will not be allowed and will show an error message:

Problem & Solution 6 – An Issue with Symbols in Your Drop Down List

You can insert the symbol from Wingdings 3 font at the right side of the drop-down list.

The Drop Down arrow will show as follows:

Problem & Solution 7 – Drop Down List Not Working While Dealing with Spaces

As you see in the following example, a dependent drop down list is created where you select the continent, and the countries’ names will be shown in the drop-down list.

If you select Asia as a continent, you’ll see a Drop Down list where the countries’ names are displayed.

The Drop Down list of the country name is Nepal.

But if you select “North America” as a continent, you’ll see no entry or value of the country name of the continent in the drop-down list.

This is because Drop Down lists are space-sensitive.

We can fix the problem by entering the following formula.

=INDIRECT(SUBSTITUTE(B13," ","_"))

Here, B13 is the cell that contains “North America”.

More importantly, the SUBSTITUTE function ignores spaces and is replaced with underscores. Then the INDIRECT function returns the cell reference and links the text value.