Compare Excel docs

pdvsa used Ask the Experts™

does Excel have the option to compare docs much like Word does?
I am using 2007.

thank you.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
If it is data you can put all spreadsheets in a single file and then compare using another sheet.

For example if you have similarly designed spreadsheets (identical layout) for ie Sales 2010 and Sales 2011 you can have one sheet that checks data between the other two sheets.

Office 2010 has the same functionality as Word - see here

2007 doesnt I'm afraid.
There are some third party compare sheets add ons listed here:

and a macro that compares two spreadsheets
pdvsaProject finance


OK I see 2007 does not have this compare functionality but 2010 does.

The macro that compares 2 spreadsheets is most interesting to me.
I looked at the link and there is the macro but then something below it and wondering if you know what this is:  
 Sub TestCompareWorksheets()

I dont know much about code.
How would it know which files you want to compare?  Maybe you can read it easily and let me know.

thank you
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
In Ole Erlandsen's TestCompareWorksheets, there are examples of comparing two worksheets in the same workbook, as well as two worksheets in different workbooks. Either way, the workbooks need to be open and the references hard-coded in the TestCompareWorksheets macro. You need to pick either the first statement (or the second) and comment out the other. Commenting out means putting a single quote in front of the statement so it doesn't execute:
'CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")     'This statement is commented out

Sub TestCompareWorksheets()
    ' compare two different worksheets in the active workbook
    CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
    ' compare two different worksheets in two different workbooks
    CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
End Sub

Open in new window

It is possible to write a different version of TestCompareWorksheets that lets the user point to the two worksheets to choose them. The advantage is that the user never needs to touch the code. If you are going to use the macro many times, it is worth the effort to write the new front end. But for a one-time use, it is much simpler just to type in the names of the workbooks and worksheets.
pdvsaProject finance


OK thank you for the explanation.  I will test it out when I  get a second.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial