How to Compare Excel Files in Excel 2016 (Step-by-Step)
A free Office suite fully compatible with Microsoft Office
Knowing how to compare two Excel files is important in any environment where a lot of people are making changes to the same file. This is common in a business environment or in the case where Excel files are shared on the cloud where many people have access to make changes.
We often need to compare the data from two different excel files, but it would be troublesome to compare each item one by one with naked eyes. So how can we compare two excel files for differences in a quick and easy way?
Compare Excel Files- Step-By-Step
Here we take two tables of employees’ performance appraisals for example. We can find the two tables are inconsistent with the name order and appraisal results.
inconsistent with the name order and appraisal results.
Now we want to know if there was a change in employees’ performance in January and February by comparing these two excel files.
Display two files side by side
Open the sheets that we need to compare. It’s troublesome to switch between two files, so we can display them side by side.
There are two methods.
Option 1 Compare Excel Files by Arrange All button
Navigate to the View tab and click the Arrange All drop-down button. There are three options for selection
Horizontal Text Box: to display two files horizontally
Cascade: to cascade to files in one screen
In this way, you can only slide these two files respectively.
Option 2 Compare Excel Files side by side
Navigate to the Viewtab and click View Side by Side.
In this way, you can check Synchronous Scrolling on the right of the View Side by Side button to scroll the two sheets at the same time or uncheck it as needed. You can also click Reset Position to reset it quickly.
Compare Excel File differences between two files
Come to either of these two files, and here we come to the January file.
Head to the Datatab, click the Manage Duplicatesdrop-down button and click Highlight unique values.
In the pop-up dialog, select Two worksheets, click Add filesto add the other file. Here we add the February file. Then check these two sheets.
In the Comparison Typearea, we can choose Whole Line Comparisonand Compare Cells in the Same Position.
Whole Line Comparison: to compare by row
Compare Cells in the Same Position: to compare by cell
Choose a color for highlight and click Confirm to mark
With these steps, you are able to compare two excel files for differences quickly and freely.
Did you learn about how to Compare Excel files in Excel Spreadsheets? To be office excel advanced, you could learn how to use WPS Office Spreadsheet online in WPS Academy. You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets, and PowerPoint Slides
You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.
Was this helpful?
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. How to compare two excel sheets and highlight differences
- 3. Check if value is in list in Excel (3 easy methods)
- 4. How to copy file names in Excel from a folder?
- 5. How to color cell based on value in Excel?
- 6. How to add month name from date in Excel formula