How do I determine what Excel-2007 is removing when it repairs my file?

Question

Summary: Excel repairs my file, tells me what was removed, I go into the xml/zip structure to investigate, and I cannot figure out what was changed. Does anybody know what I can do to better understand what Excel changed? Is it futile to try to determine? It feels like this should be possible and like I'm almost there...

Details:

When I open a file that I have renamed unnamed.xlsm, I receive the following notice: "Excel found unreadable content in 'unnamed.xlsm'. Do you want to recover the contents of this workbook? If you trust the sounce of this workbook, click Yes."

I know the file is safe, I click yes, and I receive a message that "Excel was able to open the file by repairing or removing the unreadable content." It provides the following summary, but also provides an xml file which seems to contain the same content so I did not show it. Summary:

Removed Records: Shared formula from /xl/worksheets/sheet3.xml part
Removed Records: Formula from /xl/calcChain.xml part (Calculation properties)

In order to determine issue, I have created a copy of the offending file, renamed it to have a '.zip' ending, opened up the files that Excel says it modified (sheet3), and perused the xml content, but this was not informative. I tried saving the repaired file and doing a simple diff on the xml for sheet3, but there are many changes and this is not informative either. I did the same thing for calcChain.xml and this was more useful. After saving the displayed xml with line breaks in text format, it was easy to identify the items that have been removed, but now I want to make sense of them. Perhaps they give clues of what happened to shee3. The following comparison is long, but I don't know if the entire train of differences is relevant.

FILE COMPARISON
Produced: 1-7-2011 2:42:26 PM

Mode:  Just Differences

Left file: u:\My Documents\[redacted]\calcChain_orig.xml
Right file: u:\My Documents\[redacted]\calcChain_rep.xml
812    <c r="H18" i="8" />  <> 812   <c r="N2" i="8" />
814    <c r="G18" />        +-
816    <c r="D19" />        +-
818    <c r="F19" />        +-
820    <c r="E18" />        +-
822    <c r="N2" i="8" />   +-
824    <c r="H18" />        +-
                            -+ 820   <c r="H15" />
                               821   <c r="H13" />
                               822   <c r="O19" />
                               823   <c r="O17" />
                               824   <c r="O15" />
                               825   <c r="M19" />
                               826   <c r="M17" />
                               827   <c r="M15" />
                               828   <c r="M13" />
                               829   <c r="J19" />
                               830   <c r="J17" />
                               831   <c r="J15" />
                               832   <c r="J13" />
                               833   <c r="O14" />
                               834   <c r="H18" i="8" />
                               835   <c r="G18" />
                               836   <c r="D19" i="5" />
                               837   <c r="F19" />
                               838   <c r="E18" i="8" />
                               839   <c r="H18" i="9" />
827    <c r="H15" />        +-
829    <c r="H13" />        +-
831    <c r="O19" />        +-
833    <c r="O17" />        +-
835    <c r="O15" />        +-
837    <c r="M19" />        +-
839    <c r="M17" />        +-
841    <c r="M15" />        +-
843    <c r="M13" />        +-
845    <c r="J19" />        +-
847    <c r="J17" />        +-
849    <c r="J15" />        +-
851    <c r="J13" />        +-
853    <c r="O14" />        +-
1209   <c r="H48" />        +-
1210   <c r="H62" />

Answer 1

The only real diffs after the set was sorted:

1,2c1,2
< <c r="D19" />
< <c r="E18" />
---
> <c r="D19" i="5" />
> <c r="E18" i="8" />
7d6
< <c r="H18" />
9,10c8
< <c r="H48" />
< <c r="H62" />
---
> <c r="H18" i="9" />

Looks like H48 and H62 might have been the corrupted or missing ones, the removal of which necessitated adding explicit numbers into D19, E19, and H18, whereas implicit values had been fine before. Non-Microsoft software that edits OOXML files will often do this; smarter software just deletes the calcChain file entirely and forces Excel to recreate it.

A regular diff software is always going to be a total fail for XML, use an XML diff instead. There's a large number of GUI and command-line ones, you might be able to find the elusive difference in sheet3.xml that way.