Drop-Down is a very powerful and handy tool in excel which is used to enter data in a spreadsheet from a predefined items list. This tool makes data entry very fast and accurate.
When you click the arrow to open a drop-down list on the data entry sheet, the selection might go blank. This is a really frustrating moment for those who need to input data quite fast.
If your drop-down table seems to have disappeared or not working, it could be for several reasons. You can easily fix this issue by following some steps.
Table of Contents Show
Causes of Excel Drop-Down List Not Working
- Hidden Objects
If objects are hidden in the worksheet, then the drop-down list will also be hidden. In this case, the drop-down list doesn’t function.
- Blank Cells in Source List
The drop-down list allows users to choose one value from the list. Sometimes users may leave blank cells for adding new items in the future. When there is a blank cell then the list you click also will be empty and a problem arises.
- Corrupted Workbook
The drop-down list may not work if the workbook/worksheet is corrupted.
- Unchecked In-cell dropdown
The in-cell dropdown option is checked by default. If this option is unchecked then the drop-down list may disappear.
- Drop Down list doesn’t work after Copy Paste
If you are using an older version of Excel then the dropdown list may not work. You can’t use CTRL+C(copy) and CTRL+V(paste) in the older versions but you can simply use them in the newer versions of Excel.
Fixes: Excel Drop-Down List Not Working (on Windows, Mac)
You can look at these fixes after knowing the causes of the Excel drop-down list not Working and use them to fix it.
Following fixes work both for Mac and Windows.
Unhide the Object
In Excel, objects are visible by default. If objects are hidden in the worksheet, then the drop-down list does not work.
To fix this issue in Excel, follow the steps below.
- Click on File at the top left corner.
2. Select Options.
3. After this, you’ll see a dialog box, Click on Advanced.
4. Scroll down to Display options for this workbook, and uncheck the option Nothing(hide objects).
5. Check the circle All, and click Ok.
You will get the output where the drop-down list is visible.
Delete/remove Blank Cells in Source List
Deleting empty cells is not a hard task however this method is not applicable in all cases. Sometimes you may miss every data in the workbook. There are two ways to manage blank cells.
Remove Blank Cells
You can easily remove/delete blank cells using Find and Select. To do so,
- Click Home at the top left section. Then click Find &Select at the top right corner(magnifying-glass icon).
2. Click Go to Special.
3. Choose Blanks and Click OK.
4. After this, all blank cells will be highlighted. You can now delete the highlighted cells by clicking Delete.
5. This will delete all the blank cells.
Use a Dynamic Source List
You can use a dynamic source listing instead of leaving blank cells. This will enable you to adjust cells whenever you add or remove items.
Here is a way to create a dynamic source list.
- Select a cell where you want to create the drop-down list.
- Go to Data >Data Validation > Data Validation.
3. Select List as the validation criteria.
4. Source field appears, enter the formula
=OFFSET($A$2,0,0,COUNTIF($A$2:$A$100,”<>”)).
Here, the COUNTIF function counts the total number of cells that contain data. The OFFSET function is finding the value of the cells having known starting points.
A2 is a relative reference and $A$2 is an absolute reference. Here, $A$2 symbolize the column and the row does not change when copied.
- The In-Cell drop-down option needs to be checked and click OK.
Fix the Corrupted Workbook
A Corrupted workbook can be fixed by using the repair option.
- Select the file you want to open.
- Choose the Open option.
- A dialog box appears then select Browse.
4. Locate the excel file
5. Click Open drop-down menu and select Open and Repair at the bottom.
6. Click on Repair.
7. Finally, click Close.
Enable IN-Cell Dropdown
If in cell-dropdown option is unchecked then the drop-down list may disappear. You can follow these steps to enable the In-cell dropdown.
- Open Excel and select the list.
- Go to Data from the top menu bar.
- Click on Data Validation.
5. Check the box In-cell dropdown and click Ok.
Try Paste Special
When you copy and paste a drop-down list, it may not work. If you are using an older version of Excel, you might face copy-paste issues. If it contains special characters or variables, you cannot copy and paste it. You won’t have this problem if you use a newer version.
To fix this, follow these steps:
- Copy the data.
- Right-click the cell where you want to paste the data.
- Select the Paste Special >Paste Special option in the drop box menu.
4. Check the Validation and click Ok.
Excel Drop-Down List Not Working on Sharepoint
Sometimes, you are not able to see a listing in Flow. If you have many SharePoint items on the SharePoint site in one drive folder, there might be a problem with the drop-down list. When trying to fetch items, the dropdown will disappear after some seconds, so no items will be loaded.
You can use Enter custom value option to manually type the Sharepoint list name as follow:
- Select Enter custom Value option in the site address text box dropdown.
- Copy the URL visiting the SharePoint site
- Type the SharePoint site URL in the site Address test box.
The flow designer will clean up any URL and point it directly to the SharePoint site.
If you have access to the Sharepoint site then this drop-down list must fill in the Sharepoint site automatically. You can also follow similar steps for connections such as one drive, SQL server, etc.
