You added some data validation drop down lists to your Excel worksheet, to make it easier to enter the data. Later, you opened that workbook, and the data validation arrows are missing! What happened, and how do you get those arrows back?
Select the Cell
The data validation drop down arrow only shows when you click on the cell. So, you might have 100 cells with data validation drop down lists, but you’ll only see the arrow in the active cell.
If you want to make it easy to find the cells that have data validation lists, you can colour the cells, or add a cell comment.
If you want drop down arrows that are always visible, you can use combo boxes instead of data validation.
Objects Are Hidden
Maybe there were some other shapes on the worksheet, like logos, or text boxes, and you changed a setting to hide them. Because they’re objects too, the drop down arrows will also be hidden.
To make the objects visible again, follow these steps:
- Click the Office Button, then click Excel Options.
- Click the Advanced Category
- Scroll down to the ‘Display Options for This Workbook’ section
- Under ‘For Objects, Show’, select All, then click OK
Drop Down Option
Even though the default setting for a data validation list is to show the arrow, it’s possible to change that setting. In the Data Validation dialog box, you can turn off the option for a drop down list. To turn it back on:
- Select the cell that contains a data validation list
- On the Ribbon, click the Data tab
- In the Data Tools group, click Data Validation
- On the Settings tab, add a check mark to In-cell drop down
- Click OK
Workbook Corruption
If you try all of the above solutions, and the drop down arrows are still missing, the worksheet may be corrupted. Try copying the data to a new worksheet or workbook, and the data validation drop down arrows may reappear.
Or, try to repair the Excel file as you open it:
1. In Excel, click the Office button, and then click Open
2. Select the file with the missing data validation arrows
3. On the Open button at the bottom right, click the drop down arrow
4. Click Open and Repair
5. When the confirmation message appears, click Repair.
Drop Down Arrows In Excel 2003 and Earlier
There are instructions on the Contextures website for fixing missing data validation arrows in Excel 2003 and earlier versions. Check those instructions if you’re not using Excel 2007 or later version.
Video: Missing Data Validation Arrows
To see the steps for fixing missing data validation arrows, watch this short video tutorial.
____________
You could also select cells with Data Validation on them (in Excel 2003) by pressing Ctrl+G, click Special and select “Data Validation.” Then you can highlight all of them at once.
9 years later, still a good tip!
Thanks JP, good tip!
Thanks for the clear and explicit instructions on how to fix this very frustrating ‘feature’. Why is a drop-down arrow in a data validaiton cell treated as an Excel Object in the first place – from the user’s perpective – makes little sense. Thanks again – easy to fix one you know how 🙂
Thank You
Spent hours, checked as per other sites but could not find the problem.
“Workbook Corruption” was the solution .. Thanks again
This also worked for me (November 2020!)
Thanks!
Activate objects was the solution for me. Thanks a lot!
You just restored my sanity! Activate objects kept resetting itself to HIDE. Thanks for your tip on making Excel “features” usable!
Thank you very much for the instructions. this is the only site that gave full details and options of how to fix this issue. i was able to fix it instantly.
(Excel 2010) The arrow may disappear if there is a freeze pane on the column with the data validation, and you scroll to the right. If you ensure that the column immediately to the right of the data validation column is visible, it should reappear when you click on the cell. As a workaround, you may create a spacer column to the right of the data validation column and add the freeze pane to that column.
That actually worked. All of the other suggestions did not. Who would have thought to freeze the pane next to the data validations would work. I hate Excel.
Finally – the solution that I needed! Thanks for adding that to the list, very much appreciated!
Thank you Bear!
The freeze pane reason was exactly my problem here in mid 2020. I had noticed that the adjacent column had to be visible, but had not correlated cause and effect. Now I can explain that to my clients.
Microsoft: you’re not doing yourselves any favours among your Excel fans…
Debra, this was an excellent tutorial, unfortunately it did not fix my problem (running XL2010). I finally stumbled onto what was happening, I was running a macro that copied data from one sheet to another, but first clearing all cells and deleting all shapes from the target sheet. It was the deleting shapes that was causing the problem. The only way to recover the dropdowns that I’ve found so far is to copy the defective sheet to a new sheet – then the validation arrows re-appear. Ron de Bruin has a good discussion on deleting shapes via vba (http://www.rondebruin.nl/win/s4/win002.htm ,for anyone who’s interested).
Dang drop-down arrow disappeared. I have NO idea how I did that but thanks to your simple explanation you made it quick, easy and painless to correct! I would have spent HOURS trying to find that one! Now I hours to have more beer… wait, maybe that’s why…
@Greg, glad the tip helped! And I can’t condone the use of beer, unless you brought enough for everyone.
Thank you very much ..
My file was first created as .xlsm and contained VBA codes. Then I changed it to .xls format to save space. Codes and validation worked for a year. then arrows disappeared. Tried everything, solved by saving file first as .xlsm then back as .xls
THANK YOU! I knew it had to do with hidden objects after my hour search online but nowhere did it say where the fix is. I can now sleep easy at night.
You’re welcome! Glad you’ll be able to sleep now. 😉
I tried everything mentioned here, and they still disappear.
I just discovered why my data validation drop-down arrows were disappearing. Not only is a drop-down arrow an Object, it is a Shape Object. My VBA code scrolls through all the shapes on the worksheet and deletes all those whose names do not meet certain criteria. It took some debugging in VBA to see that the arrows have names of the form “Drop Down N”, where N = 1, 2, 3, ….
All I had to do is amend the If statement that decides whether to delete a particular, chosen shape “shp”:
If And And Left(shp.Name,5) “Drop ” Then
shp.Delete
End If
Thank you very much! Hidden objects was my problem.
I guess from everyone’s thanks, this seems to happen, although it’s the first time I’ve ever encountered it. We flip files between different linguistic versions of excel, but again, we have done this for years.
It worked, thanks dear.
thank you for this articles, it’s work perfectly
That was the “Object” of my frustration!
No longer… Thank you.
Thank you so much!
Great work, other generic solutions didn’t work but this did. Thanks!
IN 2020. Thank you !
Been using data validation for years! All the sudden, my drop-down arrows disappeared. Stymied… couldn’t figure out it until I found this. Feb. 2023
Thanks, Patti, and I’m glad you found a solution for those disappearing arrows!