How to Compare Excel Files in Excel 2016 (Step-by-Step)

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

1. Horizontal Text Box: to display two files horizontally

2. Vertical Text Box: to display two files vertically

3. 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

1. 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.

1. Head to the Datatab, click the Manage Duplicatesdrop-down button and click Highlight unique values.

2. 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.

3. 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

4. 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.