Need to compare two Microsoft Excel files? Here are two easy ways to do so.
There are plenty of reasons why you might need to take one Excel document and compare it to another. This can be a time-consuming task that requires a lot of concentration, but there are ways to make it easier on yourself.
Whether you just need to take a close look manually, or you want Excel to do some of the heavy lifting on your behalf, here are two straightforward methods to compare multiple sheets.
How to Compare Excel Files
Excel makes it easy for users to put two versions of a document on the screen at once, in order to quickly establish the differences between them:
- First, open the workbooks that you need to compare.
- Navigate to View > Window > View Side by Side.
Comparing Excel Files by Eye
To get started, open Excel and any workbooks that you're looking to compare. We can use the same technique to compare sheets that are in the same document or entirely different files.
If more than one sheet comes from the same workbook, you'll need to separate it beforehand. To do so, navigate to View > Window > New Window.
This won't separate the individual sheets permanently, it simply opens up a new instance of your document.
Next, head to the View tab and find View Side by Side in the Window section.
This menu will list all the spreadsheets that are currently open. If you only have two open, they will be selected automatically.
Make your selection and click OK. You'll see both spreadsheets appear on the screen.
If it's more convenient, you can use the Arrange All button to switch between a vertical and a horizontal configuration.
One important option to be aware of is the Synchronous Scrolling toggle.
Turning this on will ensure that when you scroll one window, the other will move in sync. This is essential if you're working with a large spreadsheet and you want to continue to check one against the other. If the two sheets become unaligned for any reason, simply click Reset Window Position.
Comparing Excel Files Using Conditional Formatting
In many cases, the best way of comparing two spreadsheets might simply be to put both of them on-screen at once. However, it is possible to automate the process somewhat.
Using conditional formatting, we can make Excel check for any discrepancies between two sheets. This can save a lot of time if all you need to find are the differences between one version and another.
For this method, we'll need to make sure that the two sheets we're working with are part of the same workbook. To do so, right-click the name of the sheet you want to transfer and select Move or Copy.
Here, you can use the dropdown menu to decide which document it will be inserted into.
Select all the cells that are populated in the sheet where you want any differences to be highlighted. A quick way to do this is by clicking the cell in the top-left hand corner and then using the shortcut Ctrl + Shift + End.
Navigate to Home > Styles > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format and enter the following:
=A1<>sheet_name!A1
Just remember to sub out "sheet_name" for whatever the name of the other sheet is. All this formula is doing is checking when a cell in one sheet doesn't exactly match the corresponding cell in the other sheet, and flagging each instance.
Next, click Format and choose how you want to highlight any discrepancies. I've gone for a standard red fill. Next, click OK.
Above, you can see the results. Any cells that contain a change have been highlighted red, making it quick and easy to compare the two sheets.
Let Excel Do the Hard Work
The technique above demonstrates one way that you can let Excel handle some grunt work. Even if you're paying close attention, there's a chance you might miss a change if you were to perform that task manually. Thanks to conditional formatting, you can ensure that nothing slips through the net.
Excel is good at monotonous and detail-oriented jobs. Once you have a grasp of its capabilities, you can often save yourself some time and effort by using a technique like conditional formatting and a bit of ingenuity.
When you need to do more than compare documents, but find specific values, you should get familiar with Excel's vlookup function. For a different way to do this, try comparing files with Notepad++ instead or check out these Mac file comparison tools.