Comparing

Comparing workbooks side by side allows you to see the differences between two workbooks more easily.
This can be used on any two windows even if they are two windows on the same workbook.
This commmand can be thought of as the same as the (Window > Arrange) in horizontally but with a synchronous scrolling option to make comparing workbooks much easier.
This prevents you from having to merge all the changes into one workbook.
You can scroll through both workbooks at the same time to identify any differences.
Describing these windows as "side by side" is a little misleading as the windows are actually arranged horizontally and not side by side.
Comparing workbooks side by side (using the Compare Side by Side with command on the Window menu) allows you to see the differences between two workbooks more easily, without having to merge all changes into one workbook.
You can scroll through both workbooks at the same time to identify differences between the two workbooks.


Comparing 2 Workbooks

In Excel 2007 select (View tab)(Window group, "View Side by Side").
In Excel 2003 select (Window > Compare side by side with).

alt text

alt text

View Side by Side -
Synchronous Scrolling - Locks the two windows together wherever they currently are so that they scroll together.
Reset Window - Resets the position of the windows so they share the screen equally. The window that is currently active is the one that will appear on top.



there is no built-in comparison feature, as there is in Word to compare two documents.


More than 2 Windows Open

If there are more than two windows currently open then pressing the Compare side by side button will display the Compare Side by Side dialog box

alt text

Select the name of the workbook you want to compare the active workbook to.
This dialog box lets you select which window you want to compare to the active window.
Select the other window and press OK to display the Compare Side by Side toolbar.


Alternative to Quickly Compare Values

Depending on your needs, there can be an easier way. If the worksheets in each workbook are laid out the same, and you just want to find differences between values in the cells of each worksheet, then you can use formulas to compare worksheets. Try the following steps:


1. Create a new workbook called Compare.xls.
2. In cell A1 of the first worksheet in Compare.xls, enter the following formula:
=IF([WB1.xls]Sheet1!A1<>[WB2.xls]Sheet1!A1,"Different","")
3. Copy the formula from A1 into all the other cells that represent the range you want to compare. For instance, if you want to compare A1:G12 in both worksheets, then you would copy the formula from A1 into the full range of A1:G12.
These steps assume that the worksheets you want to compare are both named Sheet1, and they are in WB1.xls and WB2.xls, respectively. If you have other sheets in WB1.xls and WB2.xls to compare, you can use similar formulas in other sheets of Compare.xls.
When done, any cell that has the word "Different" in it represent cells that are different in the ranges being compared. Thus, if C7 had "Different" in it, then there is a difference between the cell C7 of Sheet1 in WB1.xls and cell C7 of Sheet1 in WB2.xls.
If you are comparing only numeric values between the two worksheets, you could use a different formula in step 2, above:
=[WB1.xls]Sheet1!A1-[WB2.xls]Sheet1!A1
The result is a worksheet that subtracts the values in one workbook from the other, which results in the numeric differences.


Important

The (Window > Arrange) is useful when you want to compare the contents of two similar workbooks.


© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext