Using Excel to Compare Two Sheets (for differences)
An Excel workbook only displays one sheet at a time, so comparing two Excel files (or two sheets inside the same file) can be challenging.
When a large amount of data has to be compared, this becomes more challenging and error-prone.
Thankfully, Excel has several neat capabilities that make it simple to access and compare two Excel files.
I’ll demonstrate several techniques to compare and find differences between two separate Excel files (or sheets) in this Excel lesson. The approach you decide on depends on your data’s organization and the type of comparison you’re searching for.
Let’s get going!
This instruction explains: 1. In separate Excel files, compare two Excel sheets (Side-by-Side) 2. Compare Two Sheets in the Same Excel Workbook (Side by Side) 3. Comparison of Two Sheets, Emphasizing Differences (Using Conditional Formatting) 4. Utilizing a formula, compare two Excel files or sheets and determine the differences 5. Using VBA, compare two Excel files or sheets and determine the differences 6. Utilizing an External Tool - XL Comparator
1. In separate Excel files, compare two Excel sheets (Side-by-Side)
There is a built-in function in Excel that allows you to compare two different Excel files side by side (or two sheets in the same workbook).
The option is called “View Side by Side.”
This is only advised if your dataset is small and manually comparing these files is more likely to be accurate and take less time. I advise utilizing the conditional technique or the formula method discussed later in this article if you have a sizable dataset.
Let’s look at how to use this when comparing two different files or two sheets inside the same file.
Let’s say you want to determine whether numbers in two files representing two separate months are different.
By default, a file will probably fill your full screen when you open it. You always see one Excel file at the top, no matter how little the size is.
You may open two files and then arrange them either horizontally or vertically using the view side-by-side option. This makes it simple to compare the data without having to flip back and forth.
The procedures to align two files side by side and compare them are shown below:
- The files you want to compare should be opened.
- Choose the sheet you wish to compare from each file.
- Toggle to the View tab.
- Select “View Side by Side” from the Windows group by clicking on it. Only when you have two or more open Excel files does this become available.
When you select the View side-by-side option in Excel, the worksheet is immediately set up horizontally. You may freely alter and compare these files while they are organized side by side since both files will be viewable.
If you wish the files to be arranged vertically, select the Arrange All option (in the View tab).
By doing this, the “Arrange Windows” dialogue box will pop up, allowing you to choose “Vertical.”
At this point, the other worksheet would stay in its current state if you scrolled down in one of the worksheets. This may be changed so that both sheets scroll simultaneously when you scroll in one. This makes it simpler to compare lines by line and identify any discrepancies.
However, to perform this, Synchronous Scrolling must be enabled.
Select the View tab (in any of the workbooks) and then select the Synchronous Scrolling option to enable synchronous scrolling. This button is a toggle (so if you want to turn it off, simply click on it again).
2. Compare Two Sheets in the Same Excel Workbook (Side by Side)
You can only compare two Excel files simultaneously when using the ‘View Side by Side option.
When you select the View Side by Side option while having several Excel files open, a “Compare Side by Side” dialogue box will appear where you may select the file you wish to compare with the currently open worksheet.
Open all of the files you wish to compare, then select the Arrange All option (found in the View tab), if you want to compare more than two files at once.
Select Vertical/Horizontal in the Arrange Windows dialogue box, and then click OK.
All open Excel files will be arranged in the chosen order as a result (vertical or horizontal).
3. Comparison of Two Sheets, Emphasizing Differences (Using Conditional Formatting)
You cannot utilize the View side-by-side capability if you wish to compare two different pages included within the same worksheet (as it works for separate Excel files only).
However, you may still compare them side by side.
Excel’s ‘New Windows’ function, which enables you to open two instances of the same worksheet, makes this feasible. You may set two instances side by side and then compare them once they are both active.
Assume you have a workbook in Excel with two pages, one for each of the two months (Jan and Feb), and you want to compare them side by side to observe how the sales per shop have changed:
The steps to compare two Excel sheets are as follows:
- You should compare the sheets in the workbook that is now open.
- Toggle to the View tab.
- Select “New Window” from the Window group by clicking it. This launches the same worksheet in a second instance.
- Go to the “View” tab and select “Arrange All.” The Arrange Windows dialogue box will then be shown.
- Choose “Vertical” to compare columns of data (or select Horizontal if you want to compare data in rows).
- OK.
Both of the workbook instances would be arranged vertically using the aforementioned procedures.
The identical worksheet would currently be selected in both workbooks. Choose the sheet that you wish to compare to the active sheet in one of the workbooks.
How does that function?
The identical worksheet is opened in a new window when you select New Window, but with a slightly different name. If your workbook is called Test, for instance, and you choose New Window, it will name the first instance of your workbook Test – 1 and the second instance Test – 2.
Note that the worksheet is still the same. Any modifications you make to any of these workbooks will be mirrored in the other.
And the name would change back to the original when you closed any one open instance of the file.
If you like, you may also make synchronous scrolling available by selecting it from the “View” tab’s list of options.
4. Utilizing a formula, compare two Excel files or sheets and determine the differences
While you may align the workbooks together and manually go through the data line by line using the aforementioned approach, it’s not recommended if you have a lot of data.
Additionally, manually doing this degree of comparison might result in several mistakes.
Therefore, you may leverage Conditional Formatting’s capabilities to instantly identify any changes in the two Excel sheets rather than having to do this manually.
If you have two versions in two distinct sheets and want to quickly see what has changed, this technique is incredibly helpful.
Please take note that two sheets from different workbooks CANNOT be compared.
The sheets you want to compare must be in the same Excel workbook since Conditional Formatting cannot link to an external Excel file. If these aren’t, you may compare them by copying a sheet from the other file to the open worksheet.
The procedures are as follows:
Choose the sheet’s data that contains the changes you wish to emphasize. I have chosen the information from the February sheet because I want to see how prices have changed from January to February.
On the Home tab, click
Select “Conditional Formatting” from the Styles group by clicking it.
Select “New Rule” from the menu that appears.
Click the button that says “Use a formula to determine which cells to format” in the “New Formatting Rule” dialogue box.
Enter the following formula in the formula field: =B2<> Jan!B2
Select Format from the menu.
Click the “Fill tab” and choose the color you wish to use to emphasize the mismatched data in the Format Cells dialogue box that appears.
OK
The aforementioned processes would immediately show any changes to the dataset in both sheets.
How does that function?
When the formula for a cell returns TRUE, conditional formatting highlights that cell. Using the not equal to the operator (>) in the formula, we are comparing each cell in one sheet with its equivalent cell in the other sheet in this example.
When conditional formatting identifies a discrepancy in the data, it indicates it on the Jan sheet (the one to which conditional formatting has been applied).
In this example, I’ve used relative references (A1 rather than $A$1, $A1, or A$1), so take note of that.
Keeping the following in mind, compare two Excel sheets using this way.
- This technique works well to spot discrepancies rapidly, but it cannot be used continuously. For instance, any of the datasets would produce inaccurate results if I added (or removed) a new row. As soon as I add or remove a row, all succeeding rows are treated differently and highlighted appropriately.
- Only two sheets from the same Excel file can be compared.
- All you can do is compare values (not the difference in formula or formatting).
5. Using VBA, compare two Excel files or sheets and determine the differences
Use a formula to retrieve only the values that are different across two sheets if you are only interested in rapidly comparing and finding the discrepancies.
You will want a second worksheet where you may retrieve the differences to use this strategy.
If you wish to compare two different Excel workbooks or worksheets inside the same workbook, this technique will work.
In this example, I’ll compare two datasets in two sheets (in the same workbook).
Assume you have the dataset depicted below in a sheet called Jan (along with data that is comparable on a sheet called Feb) and you are curious about the values that differ.
Create a new worksheet and call it “Difference” to compare the two sheets.
Enter the following formula in cell A1:
=IF(Jan!A1<>Feb!A1,"Jan Value:"&Jan!A1&CHAR(10)&"Feb Value:"&Feb!A1,"")
This formula should be copied and pasted for a range such that it covers the complete dataset in both sheets. I will only copy and paste this formula in the A1:B10 range because I only have a small dataset.
An IF condition is used in the formula above to look for discrepancies. When there is a difference between the values, it will return the values from both sheets on separate lines in the same cell if there is a difference, otherwise, it will return a blank.
The advantage of this approach is that it simply points out discrepancies and explains them in detail. This example makes it clear that cells B4 and B8’s prices are different (as well as the exact values in these cells).
Using VBA, compare two Excel files or sheets and determine the differences
It’s a good idea to have an Excel macro VBA code ready and utilize it anytime you need to compare Excel files or sheets if you need to do so frequently.
Additionally, you can add the macro to the Quick Use Toolbar so that you can access it with a single click and quickly see which cells differ between files and sheets.
If you wish to compare and highlight differences between two sheets, say Jan and Feb, you may use the VBA code below:
Sub CompareSheets()
Dim rngCell As Range
For Each rngCell In Worksheets("Jan").UsedRange
If Not rngCell = Worksheets("Feb").Cells(rngCell.Row, rngCell.Column) Then
rngCell.Interior.Color = vbYellow
End If
Next Angell
End Sub
The code above compares each cell in the Jan sheet with its corresponding cell in the Feb sheet by iterating over each cell in the Jan sheet (the whole utilized range) using the For Next loop. It highlights those cells in yellow if it discovers a difference (which is verified using the If-Then expression).
This code may be used in a standard module in the VB Editor.
Additionally, it’s preferable to store this code in the Personal Macro worksheet and then add it to the Quick Access toolbar if you need to do this task frequently. You will be able to perform this comparison in those methods with the touch of a button.
The procedures to acquire the Personal Macro Workbook in Excel are shown below (it is not enabled by default).
The methods to save this code in the Personal Macro Workbook are listed below.
And you can find the instructions for adding this macro code to the QAT here.
6. Utilizing an External Tool – XL Comparator
Another easy method is to use a free third-party application like XL Comparator to compare two Excel files and look for similarities and differences.
You may submit two Excel files to this web-based application, and it will produce a comparison file with the data that is common (or different data, depending on the option you chose).
Consider having two files with customer datasets (such as names and email addresses) and wanting to rapidly determine which customers are in file 1 and not in file 2.
How to compare two Excel files and produce a comparison report is shown below:
- Go to https://www.xlcomparator.net and click.
- To upload two files, use the Choose file option (the maximum size of each file can be 5MB)
- Select the Next option.
- In both of these files, choose the shared column. This common column will be used by the program to search for similarities and differences.
- Choose one of the four alternatives to receive either different or matching data (based on File 1 or File 2)
- Select Next.
- Download the data-containing comparison file (based on what option you selected in step 5)
Using a third-party tool to compare Excel files may raise privacy issues for you.
Use the alternative techniques mentioned above if your personal information requires a high level of secrecy.
Take note that the XL Comparator website states that all the files are deleted one hour after the comparison is completed.
Using these techniques, you may compare two separate Excel files (or worksheets in the same Excel file).
