Compare and merge worksheets in Microsoft Excel

Topic

New Reply

Is anybody aware of why “Compare and Merge Worksheets” is grayed out in my Microsoft Excel Tools menu bar? I have open a Microsoft Excel Worksheet with only Sheet1 filled in and the rest empty. I want to compare it with a previous version of the same document.

Regards,

JMT

Reply | Quote

Replies

Is anybody aware of why “Compare and Merge Worksheets” is grayed out in my Microsoft Excel Tools menu bar? I have open a Microsoft Excel Worksheet with only Sheet1 filled in and the rest empty. I want to compare it with a previous version of the same document.

Regards,

JMT

“Compare and Merge Worksheets” works when you share a workbook, allowing other users to modify the contents.

Reply | Quote

Please note that sharing a workbook is likely to corrupt it sooner or later…

Reply | Quote

But isn’t it like Microsoft Word where I can make changes to a document, save it as another name, and then compare it with the previous version to see all of the changes I made? How does Excel know whether the changes were made by me or by another user that I shared it with and why would Excel care?

Reply | Quote

Excel is very different from Word in many ways. It has been/is being developed by a completely different team.

Reply | Quote

So if I email the original document and my revised document to a friend, have him email me back the revised document under a different name, and then ask Excel compare the revisions with the original, can I trick Excel into thinking I shared the document with a friend and I want to view his changes?

Reply | Quote

No, not really. You’d have to turn on track changes before sending the workbook, but this automatically turns on sharing the workbook. As mentioned, sharing a workbook is dangerous, it can easily cause the workbook to become corrupted.
The alternative is to create a sheet with formulas that compare corresponding cells in the original worksheet and the edited one.

Reply | Quote

…The alternative is to create a sheet with formulas that compare corresponding cells in the original worksheet and the edited one.

What I’m hearing is that Excel doesn’t have the capability of comparing two (unshared) worksheets. Is that correct?

Could you elaborate on your suggestion? I think I could do something like this and just tell ‘Same’ or ‘Different’ for each corresponding cell. Is there a way I could display the old and new contents of the cell? Is there a way I could detect added or deleted rows?

Thanks for your help.

Reply | Quote

There is no built-in way to do it. You can do it with code. On Chip Pearson’s page http://www.cpearson.com/EXCEL/whatsnew.aspx called Compare.xla which may help you to compare…

Steve

Reply | Quote

You can do it with code.

Or formulas – I always assumed this ability is why a comparison function was not a priority in Excel.

Reply | Quote