07 July 2012

Excel Data Validation Drop Down Control Not Visible

A user found a strange problem in an Excel workbook: the data validation drop down control did not appear for any data validation cells in a specific worksheet (they appear in other worksheets). I tried the possible solutions but they did not work.

The solution came when I tried to resave the workbook as an Excel 97-2003 XLS file and the Excel compatibility checker reported some formulas were unavailable (in this case, SUMIFS) for earlier versions of Excel. After saving the workbook as an XLSX file to avoid this error, the data validation drop down control reappeared in the worksheet. It looks like opening an XLS file using Excel 2007 in compatibility mode may cause unexpected problems like this.