How to Compare Excel Files for differences in Windows

July 22, 2022
1.1K Views
0

A free Office suite fully compatible with Microsoft Office

Free Download

Given that an Excel workbook only displays one sheet at a time, comparing two Excel files can be challenging. When there is a large amount of data that has to be compared, this becomes more challenging and error-prone. Thankfully, Excel has some great capabilities that make it simple to open and compare two Excel files. 

You can compare two excel files or multiple excel files by viewing them side by side.

Compare two excel files by viewing them side by side

You can compare two excel files with view side by side feature. Comparing two excel files visually with this method is great when you have small set of data and sharp observation.

Here is the stepwise guide that you can use to compare two excel files side by side

  1. First of all, open the two workbooks that needs to be compared.

  2. Select the view tab.

  3. You will see side by side option, click it and that's it.

This is how you can compare two excel files visually. The above methods will open excel files vertically by default but you can change the position to horizontal and cascade as well. To do that simply find the arrange all option in view tab. Once you click, it will show you all the possible arrangements. Select whatever suits you.

Compare more than two excel files by viewing them side by side

You can also compare multiple excel files with the side by side feature.

  1. First of all, open all the files that needs to be compared.

  2. Select the view tab.

  3. You will see arrange all option, select the arrangement and that's it.

It will display all the open excel files. This is how you can compare more than two excel files visually.

Also, there is the feature of synchronous scrolling that allows you to scroll the excel files relative to one another for side by side comparison or irrespective of one another so that you can scroll only selected file irrespective of the other.

Comparing two excel sheets to highlight the difference

While you can align the workbooks together and manually read through the data line by line using the above method, it's not recommended if you have a lot of data. Additionally, manually performing large degree of comparison might result in several mistakes.

Therefore, you may use Conditional Formatting's power to instantly identify any changes in the two Excel sheets rather than having to do this manually.

Although you need to have both the sheets in the same excel file. In case if sheets are in different files, simply copy the sheet to the active workbook.

Here is the stepwise guide to compare and highlight difference in two excel sheets.

  1. Select the range of sheet 1 you want to compare with sheet 2

2. Click on the conditional formatting in home tab.

3. Among the several option, find new rule.

4. The New Formatting Rule’ dialog box will show up, click on 'Use a formula to determine which cells to format'

5. Use the formula: =B2Sheet2!B2 where B2 is the cell of sheet 1 that you want to compare and sheet 2 is from which we are comparing sheet 1 with.

6. Select the color you want to highlight the differences with.

7. Select OK. This should highlight the differences in sheet 1 and 2.

The above tutorials work with all WPS versions (2016,2019,etc). Every version is accessible on a variety of platforms, including Windows, Mac, Android, iPhone, etc. Excel can be used anytime and anywhere you want because it doesn't require an internet connection and may be utilized without being online.

Did you learn about how to compare excel files? 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.