How To Compare Worksheets

In this tutorial, I will be showing you how to compare two worksheets in excel. This helps you to know the different changes made in case you want to revert some few changes from the original one.

• COMPARING WORKSHEET
• Step 1 - Open the first worksheet that contains the original version of the document.

• Step 2 - Also make sure the second worksheet is visible also.

• Step 3 - Select the data in the first worksheet.

• Step 4 - In the Home tab, go to the Styles group and click on the Conditional Formatting option. From the dropdown list of options, click on New Rule

• Step 5 - The New Formatting Rule dialog box will be displayed. In this dialog box, under the heading - Select a Rule Type, click on the option - Use a formula to determine which cells to format.

• Step 6 - In the Edit Rule Description section, you will have to enter the formula inside the input field. (Format Values where this formula is true)

• Step 7 - Now enter the formula =A1<>Sheet2!A1. This formula is used to compare the current worksheet that you are from the beginning of the cell A1 in that sheet and the second worksheet that is opened in the background which is Sheet2 starting from cell A1 also.

• Step 8 - Now let's choose the type of formatting we want to give to the changes after being compared.
  Click on the Format button and this will open the Format Cells dialog box.

• Step 9 - Click on the Fill tab, select the color you want, look at the color sample and if you like it, then click on the OK button to take you back to the previous dialog box.

• Step 10 - Also click on the OK button here to finally apply the formatting rules.

• Step 11 - The formatting rule has been applied successfully.

• Step 12 - So in the first worksheet, the cells highlighted are the cells that were changed or updated. But lets now see how we can make it a little bit easy and clear to visualize in the next few steps.

• Step 13 - In that same first worksheet, click on the View tab and go to the Windows group and click the New Window option or command.

• Step 14 - Another windows will be opened. Thereby we now have two windows.

• Step 15 - Go to the first windows, in the first worksheet (Sheet1) click on the View tab. In the view tab ribbon go to the Windows group and click on the Arrange All option.

• Step 16 - A small dialog box will be displayed (the Arrange Windows dialog box). Inside the dialog box, under Arrange select the option Vertical and the click on the OK button.

• Step 17 - The two windows will now be arranged side by side. At your left hand side ,the worksheet (Sheet1) is opened with the highlighted cells which tells you where changes were made. While at your right-hand side the worksheet (sheet2) is opened with the updated changes.



TIP:

To make this comparison more visually interesting, you can also perform similar formatting rule to the second sheet and hight the updated cells changes were made.
For the second worksheet you will have to use the formula: =A1<>Sheet1!A1. The only thing that was changed is the sheet name.

• Step 18 - Now if you use similar formatting on the second sheet will a different color. You can now see where the updated changes were made in the second cell.

• Step 19 - Again, this worksheet 1 (Sheet1). The highlighted cells signifies where changes where made.

• Step 20 - Also, this is worksheet 2 (Sheet2). The highlighted cells signify the updated cells.