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

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

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:

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 drop down
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 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.

____________