Comparing two excel sheets might seem a bit troublesome at first. Generally, Excel shows one workbook at a time. But hey, it’s only difficult if you don’t know how to do it. Fear not, we’re here to guide you through the thick of it.
I’ll be frank, there’s more than a single way to go about it. Excess offers some awesome features that allow you to compare two similar workbooks at the same time. Well, there are some external methods too. As I said, there’s really nothing to worry about.
Just make sure you read through every single procedure of each step and you’ll be just peachy. Be it single worksheets or entire workbooks, we’ve got your back.
Contents
Comparing Excel Files by Viewing Side by Side
This method basically involves using the View Side by Side mode. With this option you can easily see two workbooks or two separate sheets side by side in a single workbook. This is absolutely great for visual comparison. Let’s see how it actually works.
Comparing Two Excel Workbooks
Suppose, you want to check out the sales results of your business or the ID information of your new employees in the last two months. For that you want to compare them side by side. This is really easy to do. Just follow the simple steps to execute the process.
1. The first thing you’ll want to do is, open the workbooks that you want to compare.
2. Then you’ll need to select the View tab on top of the excel file.
3. On the right side of the view tab, you’ll find the View Side by Side option.
4. Once you’ve selected that, you’ll find the Excel Workbook windows appearing side by side.
5. Now, you have the liberty to see them either horizontally or vertically
6. To do this, you have to select the Arrange All option.
7. In the popup menu, you’ll find the vertical or horizontal option. Select the option you think is convenient for you.
Note: While scrolling about two workbooks side by side, you might want to turn on Synchronous Scrolling. This will allow you to scroll through the separate workbooks at the same time. This option is generally turned on by default while using the View Side by Side mode.
Arranging More than Two Workbooks
In some cases, you might need to compare two Excel files at the same time. Even this is made possible with a few simple clicks. All you have to do is, click on the View Side by Side button. Then the Compare Side by Side dialog box will appear.
Just a small reminder, this will only work if more than two Excel files are open. In order to view all the data in the workbooks at the same time, you need to select the arrange all button in the view tab. Now, choose the viewing format and you’re done.
Comparing Two Worksheets
You might often feel the need to compare two sheets in the same Excel workbook. If you want to see those sheets side by side, follow the instructions below.
- The first thing you want to do is, go to the View tab of your workbook.
- There you’ll need to select the New Window button.
- This will open up the same Excel file in a separate window.
- Then all you have to do is, enable the View Side by Side option.
- Select One sheet in a window and another sheet in a different window. That’s pretty much it.
Comparing Two Excel Sheets for Different Values
Want to identify cells with different values? Allow me to show you the simplest way possible. After executing this, you’ll definitely be able to mark the variations. Not only that, you’ll also get the difference report in a separate worksheet.
- In order to compare two separate worksheets, you’ll have to open a new worksheet first.
- Then you’ll have to enter the formula below of cell A1 of the new sheet.
=IF(Sheet1!A1 <> Sheet2!A1, “Sheet1:”&Sheet1!A1&” vs Sheet2:”&Sheet2!A1, “”)
- Once you’re done, you’ll want to copy it downwards and to the right side through dragging the fill handle.
- Note that the relative cell reference is being used here. What I’m trying to get at is, the formula will change according to the relative position of the columns and the rows.
- This means, the formula in A1 will firstly put to comparison cell A1 is sheet 1 and sheet 2. The same goes for cell B1 and so on.
Below are the clips of the data in Sheet1 and Sheet 2
After applying the formula and dragging the fill handle, you’ll get the difference report. Which should look like the image below.
Marking the Differences with Conditional Formatting
I personally find the Conditional Formatting feature quite fascinating. Using it will allow you to highlight the differences between two sheets with the color of your own choice. Let’s talk about how it actually works.
1. Firstly, you have to select the worksheet where you want to mark all the differences.
2. Then you’ll have to select all used cells.
3. Click the first cell used in the range. This is A1 in most cases. Then you’ll have to press Ctrl + Shift + End. This will extend the selection to the very last used cell.
4. Under the Home tab of the workbook, you’ll find the Conditional Formatting option.
5. In the dropdown menu, you’ll be able to select the New Rule option.
6. You’ll be wanting to create a new rule using the following formula:
=A1<>Sheet2!A1
7. In this case, Sheet 2 has to be the sheet you’re comparing the data with.
8. There you go, the cells that have different value will get highlighted with the color that you choose.
9. The results should look like the image below where the data is highlighted in a blue shade and with bold letters.
Before we continue, let’s give you a heads up on the limitations of this particular method.
- This only works with values and can’t detect the differences between formulas and cell formats.
- This method cannot detect added or deleted rows/ columns.
- This is good for sheet level comparison only. Workbook comparisons have to be done in different methods.
Let me tell you this before we begin. We’ll be needing to use the Compare and Merge feature of Excel in this one. This is really useful when multiple individuals need to collaborate on the exact same workbook.
It allows you to see the changes and comments of all the users at the same time. How cool is that? To make full use of this feature, you might want to take the following precautions:
- The first thing you want to do is, share your Excel workbook.
- To share the workbook, you’ll want to go to the review tab and select the Share Workbook option.
- Then in the Changes group, you’ll be wanting to check the Allow Changes by More Than One User box and click on OK.
- If prompted, save the workbook.
- Each individual who makes changes to the shared workbook should have a copy of the file with a unique file name.
Now that all the preparations are complete, we’re ready to merge and compare. Just follow the steps below.
Enable Compare and Merge Workbook Feature
The first thing to do here is, adding the feature to the Excel toolbar. It’s not generally displayed in Excel by default.
- Select More Commands from the Quick Access dropdown menu.
- In the Excel Options dialog box, you’ll find Choose Commands.
- Within the list of All Commands, you’ll see Compare and Merge Workbooks.
- Select the option and click on the Add button.
- This will move it to the right-hand section.
- Click Ok to complete the step.
Compare and Merge Workbooks
Finally, we’re ready to talk about how to merge and compare workbooks. But before getting started, make sure all the users have finished working on the shared Excel Workbook. With that said, now you can merge all the copies into one work file. Follow the steps below.
- Open the primary version of the workbook that you shared beforehand.
- Click on the Compare and Merge workbook on the Quick Access bar.
- A dialog box will open up and you’ll have to select the work files you want to merge.
- Once you’re done selecting the files, press OK.
And thus, you’re done.
Review the Changes
To review the changes made by users, all you have to do is this.
- Go to the Review tab and select Changes Group.
- Then Click on Track Changes and then on Highlight Changes.
- Within the Highlight Changes dialog box, you’ll have to select All in the When box.
- Everyone in the Who box should be checked, clear the Where box, and select the Highlight changes on screen box.
- After this, click OK.
You’ll have the options to mark the changes by colors as well. And note that only copies of the same workbook will work here. Try not to merge separate workbooks.
Third Party Tools/ Addons
You might find it convenient to use some external third-party tools. These often tend to make work easier. So, if you want to merge and compare workbooks/worksheets through other means, I’d recommend the following.
- xlCompare
- Ablebits Compare Sheets for Excel
- Change pro for Excel
- Synkronizer Excel Compare
Comparing Excel Sheets in Daily Life
Let’s see in which particular cases you might need these methods and how they can help you out.
- While comparing sales reports, you might find comparing excel sheets to be really handy.
- Comparing Excel Sheets allow you to save a lot of time comparing to manually differentiate between the data at hand.
- If the sheets get marked with the differences, it becomes super easy to track changes and take steps accordingly in office work or home tasks.
Final Words
Now that we’ve covered all the methods and procedures that involve comparing two excel sheets, It’s time to part ways. I really hope I was successful in giving you a complete idea regarding every method. These should help you out a lot in your daily workflow.
Farewell for now.
Read More: