The most common reason for Excel dropdown list not working is the defective settings in your Excel. Fortunately, these issues are very easy to fix after knowing the actual cause. Start by restarting and logging on and off. You can easily fix the issues that you are facing in Excel by following some steps. For step by step instructions to fix Excel drop down list not working, read the article below.
Why is Excel Drop Down List Not Working?
How to Fix the Excel Drop Down List Not Working?
After going through the causes you can look at these solutions and use them to fix Excel’s dropdown list.
Object is Hidden: In Excel, the objects are set to show by default. But sometimes, faulty settings like objects being hidden can affect the dropdown list. When this happens, users mistake this as dropdown list not working.In-cell dropdown is Unchecked: In-cell dropdown is also one of the mere settings which most people might not have noticed but it can cause the dropdown list to not appear. The data will not show if this box is left unchecked so make sure you enable this.Bug in Windows Update: Windows update sometimes causes bugs in certain application software. ActiveX controls of excel might be disturbed which causes malfunction of certain features.
Hidden Objects
Sometimes the objects are hidden from the advanced settings due to which the values are not shown in the dropdown list. To fix this you will need to arrange the display settings. Here’s how to do it:
Open and Repair the Worksheet
Drop down list in Excel fails to work if your worksheet is corrupted. Sometimes the files also get corrupted but they can be fixed by using the repair option. To repair you will need to locate your file.
Fix the Data Validation
If your Drop down list shows blank when you click on it then it could be because you have entered blank cells in the source. You should only select the cells that contain the data from your list so you don’t face further errors. Follow these steps to locate the source and correct it:
Try Paste Special for Copy Paste
Copy Paste in Excel is troublesome if you use the older version of Excel. You cannot simply press Ctrl + C and Ctrl + V if it contains special variables and characters. But if you use the new version of Excel, you won’t have issues regarding Paste special.
Enable In-cell Dropdown
If the In-cell dropdown box is not selected in your excel then the list of values will not show or the arrow will not show. So, you select this box and try to use the dropdown list again.
Use the OFFSET Function
If the dropdown list works fine before but stops updating after you add a new item then you can use the OFFSET function. When you use this function, you will not have to keep updating the source each time you add a new item. Follow these steps below to make use of the OFFSET function. Here,
B2 is starting point of the list B:B is the column of the certain topic’s list which you are updating
Use INDIRECT & SUBSTITUTE Function
Space between the dropdown topic creates the issue of not showing the values in the Drop Down List. You can use the Indirect function to link the text value and return the cell reference. And the Substitute function ignores space and replaces them with underscores. Follow the steps below to use indirect and substitute functions.
