These comparison tools can help you spot inconsistent data, outdated values, broken formulas, incorrect calculations, and other problems in your Excel 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 perform a side-by-side comparison using Excel’s “View Side by Side” feature. Resize or rearrange the windows so you have a side-by-side view of both worksheets on your computer’s display. Again, this method is best for comparing Excel worksheets with only a few rows or columns. Excel will immediately arrange both worksheets horizontally on your computer’s screen. It might be a bit difficult to compare sheets in this landscape view, so proceed to the next step to change the orientation to a vertical/portrait arrangement. 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. Now you can scroll through both worksheets and compare them line-by-line. The major advantage of this feature is that it’s built into all Excel versions. However, you’ll still have to do a bunch of the work—i.e., spotting cells with varying figures, macros, formulas, etc.
2. Compare Two Excel Files Using Online Tools
There are web-based tools that offer Excel comparison services. You’ll find these tools beneficial if you don’t have Excel installed on your computer. This Excel Comparison tool by Aspose is a good web tool for comparing two Excel files. Upload the first (primary) Excel file in the first box, drag the other file in the second box, 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 limitation of these web-based tools is that they mostly highlight different values. They can’t highlight discrepant formulas, calculations, etc.
3. Compare Two Excel Files Using “Spreadsheet Compare”
Spreadsheet Compare is a reliable software for comparing two Excel files or worksheets. Sadly, it’s 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 your Excel app is part of the above-mentioned Office packages, you can access the Spreadsheet Compare tool via the “Inquire” add-in. If there’s no “Inquire” tab in your Excel app, here’s how to turn it on. Note: 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. That’ll launch Spreadsheet Compare in a new window, highlighting any disparity within your dataset. Regular cells with different values will have a green highlight. Cells with formulas are formatted purple while cells with a macro will sport a turquoise fill color. Select Export Results to generate and save a copy of the results to your computer as an Excel document. 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 your colleagues, team, or other people working collaboratively on the file.
Use Spreadsheet Compare as a Standalone Program
If your Excel or Office version doesn’t have the Spreadsheet Compare add-in, install the standalone software from the developer’s website. When installing the setup file, make sure you check the Register and activate the add-in in Excel box. 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.
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. It’s really that easy.
