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.
_____________
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
I found a solution that works even without creating a combo box.
ActiveWorkbook.Save
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.
Thanks very much,
I just froze the panes and it works perfectly now. Plus being a macro-free solution.
Thanks again for your tip!