This video will show you how to compare excel sheets for differences using formulas, conditional formatting, Compare and Merge feature, and a special add-in.
How two compare two Excel files: video transcript
If you compare small tables for just a few possible changes, you can view the files side by side. There is a button in Excel that lets you do just that:
- Go to View tab, find the Window group and click View Side by Side.
- Now you can see them one after the other. You can switch them to vertical view with the help of Arrange All, Vertical, and click Ok.
- As you scroll, you will see the same rows. You can switch it off using the Synchronous Scrolling icon.
If you have your tables in the same file, you need to open one of the sheets in a new window first. Use this New Window button in the same group and then enable the option to view them side by side. Pick the necessary window and click Ok. You can click the same button to return to your spreadsheet.
Find every difference in Excel sheets with a formula
If you want to see which values differ in your sheets, there is a formula that will compare each cell.
You will need a new sheet for the report. Enter the formula into A1, it may be easier to copy it from our blog post. It's an IF function with a reference to your tables:
=IF(Groups!A1<> Updates!A1, "Groups:"&Groups!A1&" and Updates:"&Updates!A1, "")
The condition here is that A1 in your first sheet is not equal to A1 in your second sheet. Type in what you'd like to see if this condition is true, for example, the word "different", or even better, show both values. Otherwise leave it blank: keep empty quotes for that and close parentheses.
When you copy the formula to the relevant columns to the right and down to as many rows as you want to compare, you will see the differences in the cells that were modified.
Compare data in Excel using Conditional Formatting
Another simple and convenient way to compare values is Conditional Formatting. It will highlight the differences right in one of the tables.
- Press Ctrl+Shift+End from A1 to select all records
- Go to Home tab and click Conditional formatting
- And create a new rule
- Find the option that lets you use a formula, and enter
- Choose how you' d like to see the updated cells and click Ok to review the differences.
However, just as in case with the formula we looked at, it checks sheets only in the same file and any data shifts like inserting a row make these methods useless.
Compare Excel spreadsheets and merge changes
If you work on the same file with several other people, there is one feature in Excel you will certainly appreciate.
The possibility to Compare and Merge changes works with a couple of preliminary steps.
- First you have to share the file before making it available to others. You can find the Share button under Review tab in excel. Click it and tick off the checkbox at the top that allows changes by more than one user. Click Ok and save the file. You can also use Track Changes instead.
- The second important condition is that everyone who can introduce changes to the shared workbook has to save a copy of this file with a unique name.
You may not see the Compare and Merge feature in your ribbon, so we'll add it from Quick Access toolbar drop-down at the top:
- Select "More commands"
- Choose from "All commands"
- Scroll down until you see Compare and Merge Workbooks, select it and click Add to get it in the window on the right. Click Ok to confirm.
Now, when everyone finishes working with the file, you need to open your original version, and click Compare and Merge Workbooks from the toolbar. You'll see the copies from other people in the dialog box. Select the files for the merge and click Ok.
All changes will be marked in your main table. This feature is very handy, you can even see what each user entered. The only limitation for it is merging the copies of the same shared file.
Compare 2 Excel files for differences
If your main file was not shared, the changes are too hard to find using the ways we described, or you know that some new records could be added to the second workbook, you will probably need to use a special extension. We've tried some tools for comparing Excel files, and I'll show you one we liked best because it offers all the options you may need without making it too complicated. It's called Synkronizer, and it is capable of comparing, merging, and updating two Excel files.
Let's take a closer look. You'll need to install it to find it under the Add-ins tab. Click on the icon to open the pane.
- The first step is simple: select the files you want to compare. Be sure you choose your main file first if you want to merge some changes.
- It places them side by side right away, and you need to pick the sheets for comparison.
- Finally, choose the way you want to compare them: as normal worksheets, compare them with links, or as a database, which is especially convenient if you have common columns.
You will also notice some additional settings at the top: the Select tab shows the content type you can compare, format to look at, and some filter options; you can get a separate report file, and highlight all changes. Let's go ahead and click Start to get a quick summary.
You can go as deep into the details as you want. Clicking on a difference you will see the value before and after. The tool will highlight the changes to cell values, formats, and show inserted rows, you can set it up the way you need under Outline in the results.
Besides simply viewing the changes, you get to transfer any of these updates. It can also quickly export the results to a new file.
There are many other add-ins that specialize in particular tasks. Like comparing files to find uniques or duplicates and looking up changes based on common values. Feel free to describe your task in the Comments section, perhaps we know just the right tool for you.