Missing Drop Down Arrows in Excel 2013

Missing Drop Down Arrows

You can create drop down lists on a worksheet with Excel’s data validation feature, and they make data entry much easier – usually! Sometimes things go wrong though, like missing drop down arrows in Excel 2013. Have you seen that problem?

Drop-Down List Arrow

In the screen shot below, there is a drop down in cell B3, and you can select Yes or No from the list.

datavalidationarrow03

That list is working well, but data validation does have its quirks. In fact, it has so many quirks that I’ve got a whole page on my website explaining what they are, and how to work around them.

And recently, I found another oddity to add to that list – missing arrows in Excel 2013.

“Missing arrows” is a fairly common problem, and usually they can be fixed by using the keyboard shortcut to show objects — Ctrl + 6

Unfortunately, that trick didn’t work this time. There are a few more fixes for missing data validation arrows on my website, and none of those worked either.

Paste a Linked Picture

Here’s how I discovered the new problem, and added a new workaround to that missing arrows page.

Last week, I was updating a file in which you can select a company name from a drop down list. Based on the company name that was selected, a company logo appears on an invoice sheet in the workbook.

The logo is in a linked picture:

  1. Copy a cell that contains a logo, or another shape
  2. Select another cell in the workbook
  3. On the Ribbon’s Home tab, click Paste, then click Linked Picture
Paste Linked Picture
Paste Linked Picture

Linked Pictures and Arrows Don’t Mix

As soon as I pasted that linked picture, the drop down arrow disappeared. I thought the workbook might be corrupted, so I tried the same thing in a different workbook. The drop down arrow disappeared there too.

The arrow appeared briefly if I pressed the mouse button when selecting the cell, but vanished as soon as I let go. The same file worked without problems in Excel 2010.

Drop Down Arrow Missing
Drop Down Arrow Missing

Vanishing Arrow Workaround

I’m using Excel 2013, on Window 8, so if you’re using a different operating system, it might not be a problem. However, if you run into this issue, here’s a kludgy workaround. If you find a better solution, please let me know.

  1. Select the cell with the data validation list
  2. Click outside of the Excel window (e.g. click on the Desktop, or click in your browser window)
  3. Click on the Excel window, and the arrow will appear, and you can select an item from the list.
Vanishing Arrow Workaround
Vanishing Arrow Workaround

_____________

44 thoughts on “Missing Drop Down Arrows in Excel 2013”

  1. Hi Debra, I was tried to recreate this bug, but in my Excel installation everything works normal. Arrow is there when cell with list validation is selected. Maybe it depends on specific Excel subversion. I’m using Win 8 and Office 365 Home premium 32-bit.

  2. Uh ho! I face exactly the same issue – Missing drop down arrow in a cell that actually has drop down list.
    And nothing works – not even the click outside of excel 🙁
    Thoughts?

  3. I’m having the same problem with Excel 2013 32-bit on Windows 7 64-bit. Data validation dropdowns worked fine until I added shapes with hyperlinks, but now I have the mysterious disappearing arrows. So far none of the solutions I’ve found work and I even tried deleting the shapes. I have found I can still select the dropdown if I move my cursor just to the right of the active cell. I watch for the cursor to change from the default Excel select mode to the northwest arrow pointer. It works, but is annoying and I certainly couldn’t expect a client to find this acceptable.

    1. What you have described is the exact problem I have. List button is there but is invisible. Did you ever manage to fix it?

  4. I have the same issue with Excel 2013 file. I have “Set Print Area” enabled to start on the column to the right of the dropdown list – my dropdown box arrow disappears. If I edit the “Set Print Area” to be a column not directly beside my dropdown box – it works again – VERY FRUSTRATING

  5. I have the same issue, very frustrating. I find if I click on the cell and then click save, the arrow reappears for that selection only. Does anyone have a perminant fix? Excel 2013

  6. I have the same issue. Excel 2013 / Win 10 pro. I too have a linked picture in my file (not on the same sheet). I found the following:
    – sheana’s solution didn’t work for me
    – the drop down arrow isnt visible but it is still “there”. Click where it would normally be and the list drops down
    – using Alt + down arrow is still a useful shortcut to show the list and that still works
    – if I delete the linked picture, all arrows reappear properly

  7. I have been searching for a solution for this for a month now and I have found a very strange solution that seems to work. My problem was stemming from a picture linked to a named indirect/index/match function which caused all validation arrows to “flash” throughout the entire .xlsb workbook.
    I created a combo box form control and set the cell link to =$1:$1048576 :all cells on one of the sheets affected by the flashing arrows. Not only did this stop the arrows from flashing on the page I made the control box on, all arrows in the whole workbook started working normally.
    I don’t know if this was an isolated success for my workbook or not but it’s worth a try to stop this frustrating bug.

    1. I had been looking for a solution to this problem for months!!!. Your solution WORKS!!!! Did you do this randomly? Both the cell link and the range, as you stated, have to be set for it to work. THANK YOU!!!!!!

      1. I found this completely randomly as I was looking everywhere for a solution. I still don’t know why it works but it could probably be used to isolate the true source of the linked image/validation arrow problem by someone who knows more about how form controls work.

    2. Matt,
      You are a genius.
      I don’t know how or why it works or how you came up with this fix.
      But it works!!!!!
      It worked for Windows 10 and Windows 7 Pro. Both with excel365 (2016)
      Thank you.
      Be well.

    3. Matt’s solution works perfectly. I wanted to put the ComboBox in the part of the WorkSheet I have hidden but that is not allowable. So, I hid it under an image.
      I also deleted the ComboBox to see if the DropDown arrows stay, but unfortunately they disappeared again.

    4. What the crap, this works! Thank you SO MUCH, Matt! You’re awesome. I have wasted so much time trying to figure this one out!
      In my case, I was using Excel 2013 on a Windows 10 machine, and I was using a simple text List for my Data Validation, but the stupid drop-down arrow would only be visible if I “clicked and held” on the cell! Once I implemented your Combo Box fix, using “$1:$1048576” for both the “Input Range” and the “Cell Link”, the Data Validation arrow simply blinks very quickly one time when I first click on it, and then stays visible until I click off of the cell!
      I wish there was a more elegant fix for this problem, but I don’t think it exists – and I don’t think Microsoft is going to do anything about it.
      Thanks again!

    5. Matt, thanks for the amazing workaround. I just wanted to add if the only reason for the having the combobox is the bug workaround, the combobox can be made invisible (and non-clickable, so you click right through to the cells behind it), and the workaround still works. To make it invisible, in VBA:
      activesheet.shapes(“Drop Down 37”).visible=false

    6. Your a champion,
      I had to set both the input range and the cell link to $1:$1048576 and had to copy the combo box form control to each work sheet with the affected drop down arrows but it works.

      If you have tried Matts solution with no success maybe try adding the input range in as well

      Thanks Matt for taking the time to post your solution !!!

    7. 2020 still this bug, and your solution, don;t know how… but still works!
      Thank you, what an elegant solution for a tiny annoying bug that was about to die my dashboard

      Cheers,
      Aleksandar

  8. To add to the last comment, it seems that the input range also needs to be set to something in the combo box.

  9. Hi
    Just to say I had exactly the same problem with Excel 2013.
    I haven’t pasted any images. But I had been playing around with Custom View & the Validation Lists and sources. Then all of a sudden the dropdown arrow only appeared when I held the left mouse button down and disappeared when I released it.
    Debra’s suggestion of selecting the cell, clicking outside if the area like the desktop), it did make the drop-down arrow appear (which was better than nothing).
    However, amazingly Matt’s solution worked! No idea why.
    Many Thanks, Bill

  10. Just a further observation I’ve noticed after I had applied Matt’s fix, is that when I select a cell which has Validation, although the dropdown arrow appears, it flashes first before appearing permanently.
    I tried an earlier version of the file & the Validation List arrows work fine & don’t flash at all ?
    Regards, Bill

  11. I do have a further update to this issue.
    As I have a lot of sheets in this workbook, I firth deleted the Combo box and the arrows disappeared again.
    Then I started to delete worksheets, one at a time. I found that the issue related to one particular sheet; once deleted, the Validation List arrows worked as before.
    But what was particularly odd, is that if I made a copy of the worksheet first, deleted the original sheet, then renamed the copy back to its original name, the issue was still corrected. Thus, it didn’t seem to be a problem with the data on the sheet.
    Any ideas ?
    Regards, Bill

    1. Bill, thanks for the update on how you fixed the problem. It’s strange that deleting the sheet worked, but a copy of that sheet didn’t cause a problem.
      Were there any names associated with the deleted sheet? Worksheet level names would have been created for the copy.

  12. Hi Debra,
    The Sheet that I copied and deleted the original etc, had five tables on it. All of the table contained live data which was being read from the main data sheet (which was called ‘TVA Returns Data’). This is the Sheet which I had been playing around with (ie. adding columns, modifying validation lists, etc).
    The table cells on the suspect Sheet used the following command to get the data:
    =COUNTIFS(‘TVA Returns Data’!$AA$3:$AA$785,”*Strain Gauge*”,’TVA Returns Data’!$A$3:$A$785, “2013”,’TVA Returns Data’!$T$3:$T$785, “>119%”, ‘TVA Returns Data’!$T$3:$T$785, “<200%")
    Regards, Bill

  13. Creating a drop-down list works fine. However, if the cursor is not directly on the cell, this cell remains empty. One cannot know that there is a drop-down menu unless one places the cursor on the said cell. It remains hidden from view. I would like it to clearly show that there is a menu there, with a command, such as “Select”! Any suggestions?

  14. Hi everyone, when sheet is protected arrows work fine. Off course cells with dropdown list need to be unlocked.

  15. Thank you Matt! Following your solution, including setting the input range to something, works for me too – but with the limitation Bill mentioned.
    I’d seen similar behaviour before with Trace Precedents arrows sometimes not appearing after clicking the button in the Ribbon. If I clicked to another sheet, then back to the first, the arrows were there! I’d never noticed a pattern to the behaviour, but now I tested it on sheets with and without linked pictures, and the pattern is the same as with validation arrows.
    So it seems that certain objects (such as linked pictures) interfere with the refresh of the drawing area ‘above’ the Excel grid, manifesting in phantom behaviour of validation drop-down arrows and formula-auditing arrows – but there could be further examples. I’ve certainly never seen this in earlier versions of Excel.
    I’m using Office Pro Plus 2013 on Win8.1 Enterprise.

  16. I just wanted to reply to this one with my FIX in case someone had the same issue. My scenario. Created a worksheet for this year and naturally I thought “I can just copy and paste that for next year.” I copied my worksheet into my other file and then the drop down arrows were lost. I actually didn’t notice it right away. After reading a bunch of other fixes that didn’t work, I created a new copy of my worksheet and voila! The arrows were on my copy. I deleted the other one. Hopefully that works for someone else.

    1. This didn’t work for me (copying the sheet and deleting the old one). I suspect that in your copying and deleting of the old sheet you broke a link between something. This whole problem is caused by the presence of a linked picture, so maybe the picture in your copied sheet was linked back to data on the original, and that link was broken when you deleted the original, converting the linked picture to a normal picture and allowing the drop down arrows to work normally.
      The above is just a guess though… unfortunately I don’t have time to test this thoroughly. The combo box workaround is good enough for me!

  17. Debra, you are a life saver. I had massive spreadsheet with validation list and all of sudden the arrows won’t show. I was ready to recreate the whole spreadsheet.
    your simple solution was amazing.
    Thanks

  18. SOLUTION BASED ON FREEZE PANES
    PROBLEM: Had the same bug with the data validation drop down button vanishing/flickering/becoming invisible.
    ROOT CAUSE: If a workbook contains a Picture that links to a Named Range, which resolves into a reference to a cell on a sheet WITHOUT Freeze Panes, then all data validation buttons will flicker on all other sheets without Freeze Panes.
    Solution A: Freeze the panes on the sheet(s) the named range refers to
    Solution B: Freeze the panes on the sheet(s) with flickering data validation validation buttons
    WHY IT WORKS: If i’m not mistaken, named ranges are recalculated on every calculation, so if you use OFFSET in your data validation list formulas, this will happen when you select the cell. The picture will then be redrawn when the named range resolves. My guess is that the visual layers in the file (pictures, frozen panes, data validation) then interact in the wrong way, hiding the data validation buttons from view when the picture is updated.

    1. Thanks very much,
      I just froze the panes and it works perfectly now. Plus being a macro-free solution.
      Thanks again for your tip!

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.