Data Validation Arrows Are Missing

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 were 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 dropdown arrows will also be hidden.

To make the objects visible again, follow these steps:

  1. Click the Office Button, then click Excel Options.
  2. Click the Advanced Category
  3. Scroll down to the 'Display Options for This Workbook' section
  4. Under 'For Objects, Show', select All, then click OK


Dropdown 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 dropdown list. To turn it back on:

  1. Select the cell that contains a data validation list
  2. On the Ribbon, click the Data tab
  3. In the Data Tools group, click Data Validation
  4. On the Settings tab, add a check mark to In-cell dropdown
  5. 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 dropdown 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.

Watch the Missing Data Validation Arrows Video

To see the steps for fixing missing data validation arrows, watch this short video tutorial.


You may also like...

21 Responses

  1. JP says:

    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.

  2. John Ramsay says:

    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 :-)

  3. JB Datta says:

    Thank You

    Spent hours, checked as per other sites but could not find the problem.

    “Workbook Corruption” was the solution .. Thanks again

  4. Alexey Fedorov says:

    Activate objects was the solution for me. Thanks a lot!

  5. Karen says:

    You just restored my sanity! Activate objects kept resetting itself to HIDE. Thanks for your tip on making Excel “features” usable!

  6. Orissa says:

    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.

  7. Bear says:

    (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.

    • steven says:

      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.

  8. Dave says:

    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 ( ,for anyone who’s interested).

  9. Greg says:

    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…


    Thank you very much ..

  11. Murat says:

    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

  12. Daria says:

    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.

  13. Randy says:

    I tried everything mentioned here, and they still disappear.

  14. Randy says:

    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
    End If

  15. Jogh says:

    Thank you very much! Hidden objects was my problem.

  16. Jimbo says:

    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.

  17. Zaka Ur Rehman Khan says:

    It worked, thanks dear.

Leave a Reply

Your email address will not be published. Required fields are marked *