Microsoft updates means new and improved features, but it also means the odd bug slipping through. In this incidental series we cover some of the recent known issues that have cropped up and how to fix and / or circumvent them.

Corrupted Data Validation Drop Down Lists

Spreadsheet users have noted that workbooks that have multiple sheets containing list-based Data Validation sometimes get corrupted or have a drop-down list which gets duplicated.

Data Validation is discussed in our Thought section >here.

This issue usually occurs when a user selects a cell with a Data Validation drop-down list on the first sheet, then switches to the second sheet and finally selects the cell that had the Data Validation drop-down list on the first sheet. After saving the file, the cell on the second sheet will permanently show a "ghosted" Data Validation drop-down list (on all subsequent times the file is opened).

This issue has been fixed in the latest release for Office 2016 update from 16.0.6965.2053 or later. To get the latest updates, click File > Account > Update Options > Update Now.

Please note that the fix should stop new files from getting into the corrupted state. However, any files that were previously corrupted will need to be repaired. To repair older corrupted files, delete and recreate the cells. You may need to copy to a new worksheet if individual cell recreation does not work. 

We will report on other bug fixes and workarounds as and when necessary. Hopefully, this will not be too regular a feature!! In the meantime, if you experience any Excel issues and require help, do feel free to drop us a line at contact@sumproduct.com - we can't promise to answer every question, but we'll try our best.