This article was last updated by on

Fixes: Excel Drop-Down List Not Working (on Windows, Mac, Sharepoint)

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.

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.

  1. Click on File at the top left corner.

click file on the the top left corner excel

2. Select Options.

Click options excel

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.

click advanced tick all ok under excel options

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,

  1. Click Home at the top left section. Then click Find &Select at the top right corner(magnifying-glass icon).

Go to home click find and select excel

2. Click Go to Special.

click go to special under find and select drop down box

3. Choose Blanks and Click OK.

tick blanks ok under go to special

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.

  1. Select a cell where you want to create the drop-down list.
  2. Go to Data >Data Validation > Data Validation.

click Data data validation under drop menu data validation excel

3. Select List as the validation criteria.

4. Source field appears, enter the formula


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.

  1. The In-Cell drop-down option needs to be checked and click OK.

select list check in cell dropdown insert formula source field ok

Fix the Corrupted Workbook

 A Corrupted workbook can be fixed by using the repair option.

  1. Select the file you want to open.
  2. Choose the Open option.
  3.  A dialog box appears then select Browse.

click open browse excel

4. Locate the excel file

5. Click Open drop-down menu and select Open and Repair at the bottom.

select open and repair under drop down open

6. Click on Repair.

select repair under microsoft excel

7. Finally, click Close.

click close under repairs

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.

  1. Open Excel and select the list.
  2. Go to Data from the top menu bar.
  3. Click on Data Validation.

select data data validation excel

5. Check the box In-cell dropdown and click Ok.

check in cell dropdown under data validation

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:

  1. Copy the data.
  2. Right-click the cell where you want to paste the data.
  3. Select the Paste Special >Paste Special option in the drop box menu.

select paste special under drop down paste special

4. Check the Validation and click Ok.

check validation ok under paste special

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like