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.
Before going into the main discussion, let me share your thoughts.
Whatever, I have created a drop-down list for “Items”.
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?
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.
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.
Ultimately, you’ll get the following output where the drop down list for “Items” is visible.
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.
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
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.
⏩ 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.
⏩ Finally, 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
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.
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?
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.
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.
As a result, you’ll get the following output without blank cells.
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.
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.
Eventually, the output will look as follows where the new entry “RAM” is automatically added without changing the source.
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.
Whenever you type “no” instead of “No”, you’ll get a message by default.
This message clarifies that the entered value doesn’t match with the delimited list.
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.
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.
However, you can settle the issue by unchecking the box before the Ignore blank option in the Data Validation dialog box and pressing OK.
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.
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.
And the Drop Down arrow will be as follows which can be easily identified from the larger dataset.
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.
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-
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.
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.
And you’ll see a drop-down list of the country name of the continent.
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.
Then check the circle before the Validation option and press OK.
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
- How to Create a Drop Down List from Another Sheet in Excel
- Create Excel Drop Down List from Table
- How to Remove Drop Down List in Excel
- How to Link a Cell Value with a Drop Down List in Excel
- How to Create Excel Drop Down List with Color
- How to Create Drop Down List with Filter in Excel
- How to Add Item to Drop-Down List in Excel
- How to Create a Drop Down List with Unique Values in Excel
- How to Copy Filter Drop-Down List in Excel
