There are two Excel documents with the same name stored in various locations on your computer. In this tutorial, we’ll show you how to compare two Excel files, even if you don’t have Excel installed on your computer, to see if the files are identical or different iterations of the same Excel workbook.
- Open Spreadsheet Compare.
- In the lower-left pane, choose the options you want included in the workbook comparison, such as formulas, cell formatting, or macros. …
- On the Home tab, choose Compare Files.
How to compare Excel spreadsheets
Here are some steps you can take if you’re trying to compare Excel spreadsheets:
1. Select “Compare files”
In your Excel program, look for the home tab to access Spreadsheet Compare. From the dialog box that appears, select “Compare files. The icon appears to be two Excel spreadsheets with magic dust and a wand on top of them.
2. Choose the blue folder icon
Next, select the blue folder icon to the right of the “Compare:” box from the smaller pop-up window that says, “Compare files.” This activates the search feature, saving you from having to manually enter the file name. You can navigate your files using the blue folder icon to find the file you want to compare. Instead of choosing the blue icon, you can also enter the web address of a shared file in the “Compare:” box if you don’t already have the file saved on your computer.
3. Search for your other file using the green folder icon
Find the green file folder next to the “To:” box so you can look through your saved files for the second spreadsheet. If your file isn’t on your computer, you can also manually enter a saved web address. If you’ve saved the files in separate folders on your computer, you can run a report on files with the same name.
6. Select what you would like to compare
Choose the items you want the program to compare from the options in the left pane of your screen. Cell formats, formulas, macros, and other options are available, or you can select “Select all” to search the spreadsheets for all potential differences. To run the comparison, select “OK. This gives you access to a two-pane grid where you can view the outcomes of your comparison. The various color highlights match the distinct changes that the program found.
Reasons to compare Excel Spreadsheets
You may review and contrast the contents of various spreadsheets for a variety of reasons. Spreadsheet Compare allows you to review and line-by-line compare the data in two workbooks. Here are some functions comparing spreadsheets can help you accomplish:
To find potential problems
You can check values and formulas using the compare feature to make sure all the information in your workbook is accurate. You can use the computed values to verify the accuracy of your manually reported values, for instance, if you manually compute each individual value in one workbook but use a formula to do so in another. It may be simpler to find things like broken formulas or incorrectly entered values using the compare feature.
To discover differences
The compare feature can help you identify any discrepancies between two versions of a workbook when you compare them. Use the Spreadsheet Compare function if, for instance, you’re using an updated document or want to ensure that a page you’re sharing matches another person’s records. Your data can be the subject of a report that the program can run to find any changes or issues.
To compare two workbooks
The feature can be used to compare two different workbooks. You can instruct the program to generate a report on a particular set of differences by providing the details. You can more easily evaluate any changes or conflicting cell values by examining differences cell by cell.
Tips for comparing Excel spreadsheets
Here are some pointers to help you compare Excel spreadsheets successfully:
Start with your older file
Start with your older file when entering the spreadsheets you want to compare. This can help you stay more organized with your work, especially if your two files have the same name. The older file’s changes can be seen on the left side of your screen, while the changes to the more recent file can be seen on the right side of your screen. Maintaining consistency in your file management and organization practices can help you make sure that you update the right document or prevent changing a master document.
Make sure your files arent password protected
You might encounter an error message that reads, “Unable to open workbook,” when you attempt to run your comparison. If this occurs, one of your workbooks might be password-protected. Select “OK,” then enter the Excel workbook password to correct this. You can save the passwords using the Inquire add-in feature within the Excel program on your computer to avoid having to do so each time you open the file. You can use the feature to enter your password and provide a description.
Understand how to interpret your reports
Learn the program’s highlighting patterns by taking a moment to do so. You can change your settings to view various details in your workbooks or spreadsheets if you want to compare more or less. The horizontal scroll bar at the bottom of the screen can be used to look through multiple spreadsheets by navigating between the pages. Additionally, you can make changes, such as resizing your cells to fit the screen if they initially appear too narrow.
Use Excels Inquire add-in
The Inquire add-in can provide additional helpful features useful for comparing files, in addition to letting you save passwords for password-protected workbooks. To use the add-in, follow these steps:
You can use this feature to examine the connections between cells, worksheets, and other workbooks. Additionally, it allows you to carry out operations like saving passwords and removing undesirable formatting from a worksheet.
Please be aware that Indeed is not connected to any of the products mentioned in this article.
How to Compare Two Excel Sheets (and find the differences)
FAQ
Can you compare two different Excel spreadsheets for differences?
You can examine a workbook, view connections between cells, worksheets, and other workbooks, and remove surplus formatting from a worksheet from the Inquire tab. Spreadsheet Compare can be launched by using the Compare Files command if you have two open workbooks in Excel that you want to compare.
How do you compare two Excel sheets and highlight the difference?
If your boss needs you to immediately determine the differences between the two Excel sheets, you can use the View Side-by-Side comparison function. You can manually determine the differences in their values row by row by selecting View Side-by-Side and selecting, for instance, this is book1 and this is book2.
