Compare two Excel sheets This can be complicated if the two are quite similar. Fortunately, we don't have to check the data one by one, because this program gives us simple and quick alternatives to make comparisons.
In addition, there are tools outside of Microsoft that can also help us with the comparison task. Read on to discover how to compare data in the most effective and easy way possible.
Why might we need to compare two Excel sheets?
This is a relatively common task in areas such as accounting or project management. Through it we seek to identify differences, find errors and validate data in order to make informed decisions.
There are many reasons why we may need to compare Excel sheets, but we will tell you the most common ones:
- Detect errors or discrepancies. When comparing two sets of data we may find incorrect entries, erroneous calculations or duplicate values. This is essential if we want to ensure the accuracy of the information.
- Conduct audits. By comparing the current version of a spreadsheet with its previous version we can see what changes have been made and check if any errors have occurred in the process.
- Validate data. If we compare the data in a sheet prepared by us with the data in a sheet prepared by a third party, we can check the information we have and verify its accuracy and consistency.
- Analyze trends. By comparing data from different periods we can identify patterns and trends that help us in decision making.
Some practical examples of comparing Excel sheets
- Accounting. To contrast payments or confirm invoices that are pending payment.
- Human Resources. It can be used to check employee attendance or analyze staff turnover.
- Marketing. In this field of activity, it is used to compare the results of different campaigns and analyze the return on investment.
- Production. One of its uses would be to compare production plans with actual production.
How can we compare two Excel sheets?
The Microsoft tool offers us several possibilities to carry out the comparison:
Parallel displacement
With this visual function we can compare two spreadsheets simultaneously, scrolling through both at the same time. This makes it easier to detect similarities and differences.
What you have to do to use this system is:
- Open the two spreadsheets you want to compare in the same Excel workbook.
- Go to the “View” tab and in the “Window” group select “New Window.”
- Within each book window go again to “View” > “Window” and “View Side-by-Side”.
- Choose the “Synchronous scrolling” option.
Once this is done, when you move the mouse pointer on the first sheet, the movement will be done the same on the second one.
Pooling values and extracting equal data
This allows us to identify data that matches both sheets. To achieve this we have several options:
use formulas
The “VLOOKUP” formula is responsible for looking up a value in the first column of each table and returning a value in the same row of a specified column.
For example, if you have names in column A of both sheets, you can use VLOOKUP to check if one of the names that appears in sheet 1 is also in sheet 2.
Match function
Returns the relative position of a value within an array. When using MATCH the program finds the row in which a value appears that is in both sheets.
Compare values
If what we need is to compare values in two different sheets and know if they are equal, greater than, less than or different from the comparison value, we can use the following formulas:
= Equal to (A1=B1)
> Greater than (A1>B1)
= Greater than or equal to (A1>=B1)
Different from (A1B1)
Conditional format
It allows us to know which cells contain different values. Select the entire table in the worksheet and enable “Conditional Formatting” in the “Home” tab within the “Styles” option.
Select the type of rule to apply and enter the formula in “Format”. You can use this one: =A1Sheet1!A1. Then choose the color in which the differences will be highlighted and click “OK”.
Compare two Excel workbooks
The alternatives we have seen allow us to compare two Excel sheets that are in the same workbook. Another option would be to compare sheets belonging to different workbooks.
You need to go to “Home and Compare Files” and click on the blue folder icon next to the “Compare” box. From there, find the location of the first book. Then go to the green folder icon next to “To” and browse to the location of the second book. Click “OK.”
In the left panel, choose the options you want to see in the comparison results or tell it to compare everything. The system will do its job and highlight the different elements for you.
Compare two Excel sheets with external tools
If you want to make the comparison easier and faster, there are external tools that are very useful in this case.
Synkronizer Excel
Compares data with great accuracy and gives you a complete report so you know which columns, rows, cells and values have different data. Plus, you can configure the tool to be as accurate as you need it to be.
Its only drawback is that it is paid. It offers a free trial, but if you have to compare spreadsheets on a regular basis, you may want to consider purchasing a license.
Ultimate Suite
This platform contains more than 60 applications for Excel and, of course, it can also make comparisons between sheets. Something especially remarkable is that it shows us the results in a very visual way, because it highlights the cells with modified data in green. The rows that are in one sheet but not in another are highlighted in blue, and Those that have been added to the second sheet are shown in red.
GroupDocs Comparison
It is a free tool that does not require you to register as a user on its website. All you have to do is upload the two Excel workbooks to compare and let it do its work. Afterwards, You can merge both files to see the difference between the two and download the results.
As you have seen, there are many options for comparing two Excel spreadsheets. You just have to find the one that best suits your needs.