The Excel Drop Down list is a very handy tool for those who analyze and keep a long record of data. When this stops working, it might be a little difficult for those who need to input data quite fast.
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?
The dropdown list on Excel could be disappearing from your worksheet due to a few reasons. You will need to go through Excel and find out the faulty settings to fix Excel dropdown list when it is not working. Below are some possible causes for the dropdown list to disappear.
- 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.
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.
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:
- Click on File located at the top left corner
- Click on the Options.
- Once the dialog box appears, click on Advanced.
- Scroll down to Display options for this Workbook.
- Check the circle of All instead of Nothing (hide objects).
- Click on OK
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.
- Open Excel
- Click on File from the top left corner
- Choose the Open option.
- Select Browse after the dialog box appears.
- Locate your Excel file and click on it.
- Change the Open option to Open and Repair at the bottom.
- Click on Repair and Close.
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:
- Click on Data from the top menu bar
- Locate and click on Data Validation
- Check the Source.
If you see the inclusion of blank cells in the source, remove it. - Correct the cell column number.
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.
- Copy the data.
- Right-click the cell where you want to paste.
- Select the Paste special option.
- Choose the Validation circle.
- Click on OK.
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.
- Open Excel
- Select the list
- Go to Data from top menu bar
- Click on Data Validation
- Select the box next to In-cell dropdown
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.
- Select the list
- Go to Data from the top menu bar
- Click on Data Validation
- Go to Settings
- Select the box under Source
- Type this command:
=OFFSET($B$2,0,0,COUNTA(B:B)-1)
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.
- Go to Data from the top menu bar
- Click on Data Validation
- Select the box under Source.
- Type this command :
=INDIRECT(SUBSTITUTE(C1,” ”,”_”))
C1 depends upon the cell column you want to validate. - Click OK.
