Close Menu
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Facebook X (Twitter) Instagram
Tech News Today
  • Hardware
    • Motherboards
    • CPUs
    • Graphic Cards
    • RAM
    • SSDs
    • Computer Cases
    • Monitors
    • Peripherals
    • Power Supply Unit
    • PC Builds
    • Computer Tips
  • Software
  • Operating System
    • Windows
    • Mac
    • Linux
  • Gaming
  • Mobile
  • Console
  • More
    • Internet
    • Networking
    • Security
    • Buyer’s Guide
    • Gadgets
    • Laptops
    • Reviews
    • How To
    • News
Tech News Today
Home » Excel

Excel Drop Down List Not Working? Try These Fixes

palisthaBy palisthaAugust 9, 2022
excel-drop-down-list-not-working

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. 

Table of Contents

  • Why is Excel Drop Down List Not Working?
  • How to Fix the Excel Drop Down List Not Working?
    • Hidden Objects
    • Open and Repair the Worksheet
    • Fix the Data Validation
    • Try Paste Special for Copy Paste
    • Enable In-cell Dropdown
    • Use the OFFSET Function
    • Use INDIRECT & SUBSTITUTE Function

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:

  1. Click on File located at the top left corner
  2. Click on the Options.
  3. Once the dialog box appears, click on Advanced.
  4. Scroll down to Display options for this Workbook.
  5. Check the circle of All instead of Nothing (hide objects).
    display-options-for-this-workbook
  6. 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.

  1. Open Excel
  2. Click on File from the top left corner
  3. Choose the Open option.
  4. Select Browse after the dialog box appears.
    browse
  5. Locate your Excel file and click on it.
  6. Change the Open option to Open and Repair at the bottom.
    open-and-repair
  7. 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:

  1. Click on Data from the top menu bar
  2. Locate and click on Data Validation
    data validation
  3. Check the Source.
    source-validation
    If you see the inclusion of blank cells in the source, remove it.
  4. 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.

  1. Copy the data.
  2. Right-click the cell where you want to paste.
  3. Select the Paste special option.
    paste-special
  4. Choose the Validation circle.
    validiation-circle
  5. 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.

  1. Open Excel
  2. Select the list
  3. Go to Data from top menu bar
  4. Click on Data Validation
  5. Select the box next to In-cell dropdown
    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.

  1. Select the list
  2. Go to Data from the top menu bar
  3. Click on Data Validation
  4. Go to Settings
  5. Select the box under Source
  6. Type this command: =OFFSET($B$2,0,0,COUNTA(B:B)-1)
    offset

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.

  1. Go to Data from the top menu bar
  2. Click on Data Validation
  3. Select the box under Source.
  4. Type this command : =INDIRECT(SUBSTITUTE(C1,” ”,”_”))  
    indirect
    C1 depends upon the cell column you want to validate.
  5. Click OK.
fix
palistha

Hello! It's Palistha. I work as a content writer who believes in originality and quality. I'm also a student so, that humbles me into "still learning" phase. In my free time, I obsess over sci-fi and food.

Related Posts

check mark in excel

How to Put Check Mark in Excel

January 20, 2023
best excel shortcuts

Best 52 Excel Shortcuts Only Experts Know

January 19, 2023
how to find unique values in excel

How to Find Unique Values in Excel

January 15, 2023
reference isn't valid excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

January 6, 2023
excel find function not working

Excel Find Function Not Working? Here’s How to Fix it

January 4, 2023
excel trim function not working

Excel Trim Function not Working? Try these Fixes

January 3, 2023
Add A Comment

Leave A Reply Cancel Reply

Latest Posts
scanning and repairing drive stuck

Windows Stuck in Scanning and Repairing Drive Screen? 4 Easy Fixes

December 4, 2023
computer died

Computer Died? Here’s What You Should Do Next

December 1, 2023
how-to-share-a-printer

How to Share a Printer on Network (Step-by-Step Guide)

November 28, 2023
You may also like
printer-not-printing

How to Fix a Printer That’s Not Printing

September 26, 2023
Share Location With Friends and Family

Share Location With Friends and Family (iPhone and Android)

September 18, 2023
how-to-print-without-a-printer

How to Print Without a Printer

September 18, 2023
Recommended
Cookie Clicker Garden Guide

Cookie Clicker Garden Guide to Unlocking Every Seed

September 26, 2021
monitor no signal

Computer Turns On But Monitor Says No Signal (9 Ways To Fix)

November 10, 2022
Facebook X (Twitter) Pinterest
  • Home
  • About Us
  • Our Team
  • Editorial Guidelines
  • Privacy Policy
  • Affiliate Disclosure
© 2023 TechNewsToday, editor@technewstoday.com | Tech Central Pvt. Ltd.

Type above and press Enter to search. Press Esc to cancel.