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

Get FREE Advanced Excel Exercises with Solutions!

There’s no denying that drop down list is a powerful feature in Excel. But sometimes we face issues that hamper working with the drop down list. In this article, I’ll point out 8 significant reasons why the drop down list is not working in Excel and demonstrate their solutions.


Excel Drop Down List Not Working Issues & Their Solutions

Let’s consider the following dataset where some items are given with their order ID, states of U.S., and sales.

Dataset

Before going into the main discussion, let me share your thoughts.

Whatever, I have created a drop-down list for “Items”.

Drop Down List in Excel

Now we’ll discuss the issues for what drop down list not working in Excel with their solutions.


1. When Drop Down List is not Visible

Sometimes, Drop Down lists may disappear due to many reasons. But it is crucial to make visible the drop down list.

1.1. If Objects are Hidden

Look at the following figure and the drop down list is not visible. Can you guess the reason behind this problem?

If Objects are Hidden

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

Then, you’ll see the following dialog box namely Excel Options, and move the cursor on the Advanced option.

If Objects are Hidden

As you see the Nothing (hide objects) option is checked, but the drop down list is not visible.

So you have to uncheck the option and check the circle of the All option like the following figure.

If Objects are Hidden

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

Drop Down List in Excel


1.2. Checking the Drop Down Option

Like the previous problem, the Drop Down list may not appear. The reason behind the disappearance is different from the first issue.

Checking the Drop Down Option

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.

Checking the Drop Down Option


1.3. Highlighting The Cell

Truly speaking, creating a drop-down list saves your time and makes the cell dynamic. Unfortunately, the tool has some default limitations.

For example, if you move the cursor from the drop-down arrow, the sign of the arrow will disappear as shown in the following picture though the “Items” has a drop-down list.

So, we may use Combo Box to highlight the drop-down list.

Follow the steps below.

Steps:

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

⏩ Draw a box in the following way.

Highlighting The Cell

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

Highlighting The Cell

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

⏩ Finally, enter OK.

Highlighting The Cell

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

Highlighting The Cell


1.4. If Workbook is Corrupted

Another reason why the drop-down list may not appear if the 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.

If Workbook is Corrupted

Read More: How to Edit Drop Down List in Excel


2. Showing Blank in Drop Down List

In certain cases, you may notice blank cells after clicking on the drop-down arrow.

Do you have any idea about this circumstance?

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.

Showing Blank in Drop Down List

As you see the source in this instance is $B$5$:B$19. That means there are some blank cells in the source and this is the main reason.

Whatever, you may fix the source by removing blank cells like the following way.

Showing Blank in Drop Down List

As a result, you’ll get the following output without blank cells.

Showing Blank Cells in Drop Down List


3. Problem with New Entry (Auto-Updating of Drop Down List)

We’ll now focus on the problem of auto-updating for which you may notice the drop-down list is not working in Excel.

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

It is quite time-consuming to fix the source of data validation every time.

Problem with New Entry

So, we can make a dynamic range by utilizing the OFFSET function like the following way

Input the below formula in the Source option.

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

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

Problem with New Entry

Eventually, the output will look as follows where the new entry “RAM” is automatically added without changing the source.

Problem with New Entry


4. If Valid Entries Are Not Allowed and Drop Down List Not Working

As you see there is a delimited list in the following example where three types of cells are available in the drop-down list.

If Valid Entries are not Allowed

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

If Valid Entries are not Allowed

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

If Valid Entries are not Allowed

Indeed, the delimited list in the drop-down list is case-sensitive.

So, the way to fix the problem is to enter the exact value with upper and lower case from the drop-down list.


5. If Invalid Entries are Allowed and Drop Down List Not Working

Unlike the valid entries sometimes the invalid entries are allowed in the Drop Down list.

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

If Invalid Entries are Allowed

So, what’s the cause of this error?

If you look closely there is a blank cell in the lower part of the “Items” list which allows the invalid entry.

If Invalid Entries are Allowed

However, you can settle the issue by unchecking the box before the Ignore blank option in the Data Validation dialog box and pressing OK.

If Invalid Entries are Allowed

After doing that, if you input the entry “Smart Phone”, the invalid entries will not be allowed and will show an error message by default.

If Invalid Entries are Allowed


6. An Issue with Symbol of Drop Down List

Though it is a minor issue, it may become necessary when you have a larger dataset and you want to identify the drop-down arrow rapidly from the larger dataset.

Likewise the following way, you may insert the symbol from Wingdings 3 font at the right side of the drop-down list.

Issue with Symbol of Drop Down List

And the Drop Down arrow will be as follows which can be easily identified from the larger dataset.

Issue with Symbol of Drop Down List


7. Drop Down List Not Working While Dealing with Spaces

At this moment, I’m showing an interesting and also significant issue while dealing with spaces in the drop-down list.

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.

Issues with Spaces in Drop Down List

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

Issues with Spaces in Drop Down List

The Drop Down list of the country name is-

Issues with Spaces in Drop Down List

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.

Issues with Spaces in Drop Down List

The matter of fact is that the Drop Down list cannot ignore the space between the words

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.

Issues with Spaces in Drop Down List

And you’ll see a drop-down list of the country name of the continent.

Issues with Spaces in Drop Down List

Read more: How to Make Dependent Drop Down List with Spaces in Excel


8. Drop Down List Is not Working After Copy Paste

In the earlier version of Excel, you may not copy and paste the Drop Down list keeping with the source formatting.

Though you can simply use the CTRL+C (copy) and CTRL+V (paste) in the updated version of Excel.

If you use the earlier version, select the Paste Special option while pasting the data.

Drop Down List is not Working After Copy Paste

Then check the circle before the Validation option and press OK.

Drop Down List is not Working After Copy Paste

If you do that, you’ll get the copied dataset with the created drop-down list in the earlier versions of Excel.


Things to Remember

i. Save the Excel file in .xlsx format to keep the drop-down list while opening the file in the updated version.

ii. Try to remember the field name or the place where you created the drop-down list. But if you think that it might not be possible to remember, you may use the highlight the cell by inserting a combo box or symbol.


Download Practice Workbook


Conclusion

These are the issues you may get troubled by while working on the drop-down list. I also covered the solution along with the issues. Really, I hope this article might be beneficial for you. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.


Further Readings


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo