12

I am using Excel 2010, and while saving a workbook as .xls, I get the usual compatibility report:

Some cells or styles in this workbook contain formatting that is not supporten by the selected file format. These formats will be converted to the closest format available.

I usually don't care much about it, but this time I need to guarantee that a user of a previous version will see what I see.

How could I find the offending cells?

9
  • 1
    Any chance clicking "Help" will actually reveal it? And what does "Copy to New Sheet" give you? (Unrelated, but surprisingly unknown to many users: clicking the blue "More info" link in a Windows error report, sometimes takes one to very detailed information about the issue and its solution!)
    – Arjan
    Aug 14, 2011 at 8:25
  • I agree with @Arjan the help/More info should pull out the detailed info (I recall doing something like that)
    – Sathyajith Bhat
    Aug 14, 2011 at 9:16
  • 2
    @Arjan @Sathya When I press help I get to this page. In there, there are a few probable causes for the incompatibility, but I find no clue on how to get Excel to tell me which cells are actually causing the message. My workbook is really big, and trying to test it incrementally will take me days :( Aug 14, 2011 at 9:37
  • @Arjan "Copy to New Sheet" works flawlessly ... Aug 14, 2011 at 9:40
  • So, does "Copy to New Sheet" give you an explicit list of the problems?
    – Arjan
    Aug 14, 2011 at 9:51

2 Answers 2

6

As “Copy to New Sheet” has found no specific errors, the problem is probably not very localized.

One possible reason from How to fix/eliminate MINOR LOSS OF FIDELITY errors :

The "loss of fidelity" is likely the result of different color definitions used between the two versions. If you want to save the workbook in the older format, you need to use only colors that are defined in the older versions of Excel. To see these, click on the Office button, pick Excel Options from the bottom of the frame, and go to the Save tab. Click on the Colors... button, and you'll see the palate available for the older versions. You can see the RGB values for colors in the palate by choosing one, clicking the Modify... button, and then going to the Custom tab. To avoid the annoying "loss of fidelity" message, you have to use colors with exactly the same RGB values for the colors in that palate. Check fonts, fills, and lines on all sheets in the workbook.

3
  • I agree. I only seem to see this when I apply transparency to a color and then save the workbook to an earlier version 2003 or earlier.
    – wbeard52
    Jul 21, 2012 at 16:56
  • Believe transparency was my issue as well. Alas the compatibility checker only told me there were 2 issues, but did not give a 'fix' option. The help gave generalised information. As I only had a small amount of cell background formatting, I applied a background of 'no fill' to all cells which resolved the issue. I then just re-applied colour backgrounds where I had them set. Jun 15, 2015 at 0:37
  • MS deliberately creates these compatibility problems with colors because the proposed color palette has been changed. The solution proposed by harrymc works fine.
    – Pino
    Sep 15, 2015 at 6:51
1

From the 2007 help, when manually running Compatibility Checker:

In this box, the Compatibility Checker lists all of the compatibility issues it found, such as new or improved features or functionality that you used in an Office Excel 2007 workbook that are not supported in an earlier version of Excel. The box also lists the number of times that an issue occurs in the workbook. You can click Find to locate the issues.

3
  • Well, it does not do that. It just shows a format incompatibility message, specifying that there are four occurrences. (Thanks anyway!) Aug 14, 2011 at 10:04
  • @belisarius, did you try manually?
    – Arjan
    Aug 14, 2011 at 12:20
  • Yes. Same result. I think the problem is in some of the blocks with conditional formatting ... if only I could identify in which ones! Aug 14, 2011 at 17:57

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .