The simplest way to compare two Excel worksheets is just by looking at them. If you use a standard widescreen monitor there is enough room to display two files side by side.
Compare workbooks side by side
To compare two Excel files side by side is to use the Split Window function.
- Open two Excel files you want to compare.
- Select the first file.
- Use the Windows button + Left arrow to move it to the left side of the window.
- Choose the second file from the right side of the screen.
Now, you can compare two files side by side.
Arranging multiple sheets
If you want to arrange multiple sheets in Excel, you have to open these files, navigate to the first one and click View >> Windows >> Arrange All.
Select vertical arrangement and click OK.
You can scroll each of them independently.
Compare workbooks side by side inside Excel
If the previous way of comparing files is not enough for you, you can use the Excel feature called View Side by Side. It’s located under View >> Windows.
When you have two Excel files opened, Excel will automatically display them in the horizontal view. If you have more than two, you will have to choose the file from the list.
Select the file and click OK.
Horizontal view in the wide-screen monitor is not the best choice. Switch it to the vertical position. To do this, click View >> Window >> Arrange All. Choose Vertical and press OK.
At first, it seems that we’ve achieved the same result as before. But now, if you scroll one window, the other one will scroll to the same position. If you want to disable this feature, go to View >> Window and uncheck Synchronous Scrolling.
Now, you can scroll worksheets independently as you did in the first example.
Compare worksheets side by side inside Excel
If the sheets you want to compare are inside a single workbook, click View >> Window >> New Window.
It will open the same Excel file in two windows. If you look at the top of the workbook, you will notice that both of them have the same name, but with different numbers.
By default, both of them have the same worksheet selected. If you change something in one window, it will automatically be displayed inside the other one.
Now, you can switch to the worksheet you want to compare inside another workbook to View Side by Side with Synchronous Scrolling.
Conditional formatting
With the conditional formatting, you can highlight cells that have different values in two sheets. It’s important that you do it exactly as I show you because Excel likes to change a formula you enter. So be careful.
- Click inside data area and press Ctrl + A to select all cells inside this range.
- Navigate to Home >> Styles >> Conditional Formatting and choose Manage Rules.
- Click the New Rule button.
- Choose a rule type and insert the following formula: =A1<>Sheet2!A1 into the rule description.
- Click the format and choose the color to fill the cells.
- If you have all the values set, confirm changes by clicking the OK button.
- Click OK.
- Normally, you would click OK to execute the rule, but instead, select the rule and click Edit Rule.
- As you can see the formula has changed and will give us incorrect results. Enter the correct formula one more time and click OK. Then click OK one more time.
- All the cells inside Sheet1 that are different than the same cells inside Sheet2 are highlighted.
Highlight differences with VBA code
This time let’s highlight the cells with a simple click. Let’s write some VBA code.
The following procedure is going to highlight cell differences.
Select cells inside Sheet1 that you want to check and run the following macro.
1 2 3 4 5 6 7 |
Sub HighlightDifferences() For Each cell In Selection If cell.Value <> Sheets(2).Range(cell.Address).Value Then cell.Interior.Color = RGB(255, 217, 102) End If Next cell End Sub |
This procedure loops through each cell inside the selection and compares the value of this cell with the cell in the second worksheet. In our case it’s Sheet2. If cells are different, the cell inside Sheet1 is filled with color.
The result is the same as in the previous example, but this time it’s only one click.