Fortunately, there are a number of ways to compare two Excel files. In some cases you can merge the changes made in two different Excel files into one single file.
How to Compare Two Excel Files
If you have two Excel files with multiple sheets, the easiest approach is to do a full worksheet comparison using third-party tools. You can find a few tools online that help you compare Excel files, but there are a few you can use for free. One of those is Spreadsheet Compare, available from SourceForge. Download and run the app, which installs an add-on in your Excel application. To use Spreadsheet Compare to compare two Excel files: This tool is a powerful way to compare entire Excel worksheets and see quick results with all the changes. You can scroll through and keep or remove the changes you want to keep.
Use Excel to Compare Two Sheets
If you have individual worksheets in an Excel file to compare, you can do this in a few different ways. One is to use formulas to create a third spreadsheet showing all of the differences. Another is through conditional formatting to highlight cells that have changed.
Compare Excel Spreadsheets Using Formulas
If you have two sheets that you want to compare, you can create a third sheet that highlights which cells are different and shows you the difference. This is accomplished using an IF formula. Using formulas is one of the easiest ways to compare individual sheets, because you don’t have to alter the original sheets in any way.
Compare Excel Sheets With Conditional Formatting
Another approach to comparing two sheets is using conditional formatting. As you can see from the results sheet above, with all cells using the same formatting, it can be difficult to spot the differences between sheets. You can use conditional formatting to identify the differences.
Compare Excel Worksheets Manually
One simple and quick way to compare two worksheets is by doing so visually. Excel provides an easy way to visually compare two sheets side-by-side. Side-by-side viewing is especially useful in those situations where two worksheets are mostly the same, but you know there are a few differences. Synchronized scrolling lets you scan down the spreadsheet visually to find those differences.
Viewing Side-by-Side in Excel 2010 and Earlier
If you are using an earlier version of Excel, the individual worksheet files open in the same Window. You can still use the View Side-by-Side feature, but finding it is a little different.
Options Make It Easier to Compare Two Excel Files
The option you use to compare data in Excel depends on the volume of data and where its stored. If you have multiple sheets in two different Excel files, your best option is to use a third-party add-on like Spreadsheet Compare. However, if you’re only looking to compare two or more sheets inside one workbook, any of the other options work well. Use formulas and conditional formatting for large spreadsheets with just few small changes scattered throughout that you need to find. Use the visual side-by-side approach if you have two spreadsheets with lots of data changes that are easier to identify visually.
