Workbook Loses All Conditional Formatting

Michael has a workbook that he created using Excel 2007. If he opens the workbook on a system using a later version of Excel (such as Excel 2013), then all conditional formatting is erased in the workbook. Michael wonders why this would be happening.

More than likely, this is a compatibility issue rearing its head. Open the workbook in Excel 2007 and check to see if it is saved in "compatibility mode." If it is, use Save As to save in native Excel 2007, most likely in XLSX format. Then, open the file in Excel 2013 and see if the same problem exists.

If it does still exist, then see if you can find someone using Excel 2010 and try opening the workbook in that version. If the problem is not evident, then use Save As on that system to save as a new workbook, again making sure you don't use compatibility mode. You can then try, again, opening in Excel 2013.

If the problem still manifests itself, it is probably time to think long and hard about biting the bullet and upgrading to a much more recent version of Excel and recreating the workbook from scratch. (Nobody likes to do such recreation, but sometimes it is necessary.)

This tip specifically addresses Michael's case, where he references moving a workbook from Excel 2007 to Excel 2013. It is equally applicable, however, if you are wanting to move a workbook to a much later version of Excel. The same process—to incrementally move the workbook from one version to the next later version—should be followed. Of course, if you need to "step" a workbook through four or five different versions of Excel, you might find the hassle in doing so to be more work than simply recreating the workbook in whatever version of the software you happen to be using.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13021) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.