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.
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:
- Copy a cell that contains a logo, or another shape
- Select another cell in the workbook
- On the Ribbon’s Home tab, click Paste, then click 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.
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.
- Select the cell with the data validation list
- Click outside of the Excel window (e.g. click on the Desktop, or click in your browser window)
- Click on the Excel window, and the arrow will appear, and you can select an item from the list.
_____________
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
This may be a dumb question, but does Microsoft ever actually fix anything in Excel?
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?
Hi everyone, when sheet is protected arrows work fine. Off course cells with dropdown list need to be unlocked.
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.
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.
Thanks Matt!
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!