Excel can be a powerful tool for data analysis, but it can also be prone to errors. These comparison tools can help you identify and fix problems in your worksheet. ..
1. Compare Two Excel Sheets: View Side by Side
If you can peruse the sheet’s data at a glance, open them in a separate window and compare them side-by-side using Excel’s “View Side by Side” feature.
To compare Excel worksheets side-by-side, resize or rearrange the windows so that they are both on your computer’s display. This will give you a side-by-side view of both sheets.
To change the orientation of your worksheets to a vertical/portrait arrangement, follow these steps:
- On your computer, open Excel and click on the File tab.
- In the left pane, click on the Options button and then on the View tab.
- Change the orientation of your worksheets to a vertical/portrait arrangement by clicking on the Change Orientation button in the right pane.
That’ll stack both worksheets in a side-by-side position on your screen. There’s one more setting you need to enable to make the comparison a lot easier.
If the top rows of both worksheets are headers, make sure you freeze them so they don’t move with the rest of the dataset when you scroll through. ..
You can now compare the two worksheets side-by-side. This is a major advantage because it’s built into all Excel versions. However, you’ll still have to do some work—i.e., spotting cells with varying figures, macros, formulas, etc.—to make sure the comparison is accurate.
2. Compare Two Excel Files Using Online Tools
Aspose’s Excel Comparison tool is a helpful online tool for comparing two Excel files. ..
Compare the two files and select the Compare Now button.
If the files have multiple sheets, select the sheets you want to compare in the Sheets tab. If both sheets have cells with different values or content, the Aspose Excel comparison tool will highlight the differences in yellow.
The limitations of these web-based tools are that they largely focus on different values. They can’t highlight discrepant formulas, calculations, etc.
3. Compare Two Excel Files Using “Spreadsheet Compare”
Spreadsheet Compare is a software that can be used to compare two Excel files or worksheets. Unfortunately, it is only available for Windows devices at the moment. It comes as a standalone program and is also built into Microsoft Excel included in the Office versions/packages: Office Professional Plus (2013 and 2016) or Microsoft 365.
Use Spreadsheet Compare in Excel
If you’re using an Office app that’s part of the Office suite, you can use the Spreadsheet Compare tool. If there’s no “Inquire” tab in your app, here’s how to turn it on.
If you don’t find an “Inquire” checkbox in the COM Add-ins page, your Excel or Office version doesn’t support Spreadsheet Compare. Or perhaps, your organization’s admin has disabled the feature. Install the Office versions with Spreadsheet Compare pre-installed or contact your organization’s admin. ..
To launch Spreadsheet Compare, open your spreadsheet and click on the green “Compare” button in the toolbar. This will open a new window with your data highlighted in different colors to indicate any disparities. Cells with different values will be green, cells with formulas will be purple, and cells with macros will be turquoise. ..
Export Results will generate a copy of the results in Excel format. ..
The report will indicate the sheet(s) and cell references with varying datasets, as well as the exact values of the old and new data.
You can share the Excel report with other people working on the file by copying and pasting the data into a new spreadsheet or by creating a new sheet in Excel.
Use Spreadsheet Compare as a Standalone Program
If you’re using Excel or Office, you can’t compare data in spreadsheets without the add-in. To install the add-in, you’ll need to register it and activate it in Excel.
After installation, launch Spreadsheet Compare and follow these steps to use the program to compare the Excel documents. ..
Spreadsheet Compare will process the files and highlight cells with varying values in green. If there are differences in the values of cells, Spreadsheet Compare will show them in a table.
Spot the Differences
The “View Side by Side” comparison tool is the most viable option for Office Home or Students users. If you’re using Excel for Microsoft 365 or Office Professional Plus (2013 or 2016), the built-in “Spreadsheet Compare” tool is at your disposal. But if you don’t Excel on your computer, web-based Excel comparison tools will get the job done.
