Looking for the solution a how to compare two excel files and find the differences but you don't know how to do it? Here are three simple ways to do it.
There are several reasons why you may need to take an Excel document and compare it to another. It may take some time and a lot of concentration, but with the advice in this guide you will find that there are solutions that will help you make it really quick and easy.
How to compare two Excel files with the Tile option
Excel makes it easy for users to compare two document versions on the screen in order to quickly determine the differences between them first open the workbooks you want to compare and then click on the menu View> Tile (on the Window tab).
You can use the same technique to compare two Excel sheets that are in the same document or in different files. If more than one sheet comes from the same workbook, you'll need to separate it first by going to View> New Window and open a new instance of your document. Then go to the tab Show and click on Tile in the tab Window. This menu will list all currently open worksheets for you. If you have only two open, the only two open will be automatically selected.
Make your selection and press on OK. You will see both worksheets appear on the screen side by side. For convenience you can also use the button Arrange everything to switch between configurations vertical to horizontal o superimposed.
One option to be aware of for manually comparing two Excel files is the command Synchronous scrolling, always on the menu Show. Activating it, when you scroll one window, the other will move in sync. This is important if you are working with a large spreadsheet and want to check it carefully. If the two sheets, for whatever reason, are not aligned, click on Reset window position to solve.
How to compare two tables in Excel
How to compare two Excel files with Conditional Formatting
using the conditional formatting you can have Excel check the differences between the sheets of the two files for you. This can save you some time if all you have to do is find just the differences between one version and another.
To achieve this, it is necessary to make sure that the two sheets belonging to two different workbooks are part of the same workbook. Right-click on the name of the sheet you want to transfer and select Move o Copy.
Here you can use the drop-down menu to decide which document to insert into. If the sheets have the same name, with the right mouse button on the sheet name you can optionally rename it by selecting Rhinomine.
Select all the cells that are populated in the sheet where you want to highlight any differences. A quick way to do this is by clicking on the cell in the top left corner and using the shortcut Ctrl + Shift + End.
Then go on Home> Conditional Formatting> New Rule.
Select Use a formula to determine which cells to format and enter in the text box of Format the values for which this formula returns True, the following code:
= A1 <> Sheet_Name! A1
Just remember to replace the word "Sheet_Name”With the real name of the other sheet. All this formula is doing is checking when the cells on one sheet don't exactly match the corresponding cells on the other sheet, and mark each difference.
Then click on the button Size and choose how you want to highlight any discrepancies. I recommend that you use the standard red color in the fill tab. Then, click on OK.
Any cells that are different will be highlighted in red, making it quick and easy to compare the two sheets.
How to merge two Excel cells
How to compare two Excel files with an add-in
If you don't know it in Excel you can enable, if it is not active by default, the add-in Compare Files.
To activate it go up File> Options> Additional Components. In the Add-ins table you will see all those currently enabled on Microsoft Excel. Among these, check for the presence of the voice Inquire.
If it is not present, go to the bottom and in the drop-down menu Manage, select the voice COM Add-ins then press the button vai.
In the box that appears, check the item Inquire I awarded her OK.
By doing so you will have enabled a new tab in the Excel toolbar, called Inquire.
How to insert the Developer tab in Excel
Now make sure you have the two workbooks open in Microsoft Excel and on the Inquire tab hit the button Compare Files, in the tab Compare. In the box that appears, press the button again Compare and check the boxes Compare e That's it the names of the two Excel files to compare.
You will notice that the two Excel files appear side by side and the different cells are colored in order to highlight the differences. In the lower part of the screen on the left there is a legend that illustrates the meaning of each color. By placing a check mark on a color, in case of variation Excel will show the cell with that type of coloring.
For example in our case by ticking the box Entered Values, all those cells where the values are different between the two Excel sheets are shown in green.
Always at the bottom, but in the center, the differences box is visible with indication of the cell, value and type. Finally on the right, at the bottom, a graph is visible with an indication of the amount of variations found.
How to view the formulas of an Excel sheet
Programs to compare Excel files
If you don't have Microsoft Excel and you want to compare two Excel files, you can use third-party software such as:
DiffEngineX: For row and column operations, requires the selection of a primary key for each sheet and for both rows and columns.
Synkronizer: In addition to xltrail, it is the only tool that correctly offers all the changes on the sheet without any manual intervention. It shows them by arranging the original workbooks next to each other and changing the cell colors (in both files).
XTrail: is a Web Based tool similar to GitHub which not only compares 2 Excel files, but also keeps the changes of the two versions in an overview page with all the changes to the VBA code in one page (red means old / deleted while green means new / added).
