How to compare two excel sheets and highlight differences

In this article, you will know how to quickly compare two excel sheets and highlight differences with only 6 simple steps as follows. 

How to quickly compare two excel sheets for differences in values?

You can use the View Side-by-Side comparison function if your boss requires you to immediately find out the differences between the two Excel sheets.

For example, this is book1 and this is book2, click on View Side-by-Side and you can manually find out the differences in their values row by row.

But if you are dealing with a large amount of data, this would not be a good way to do it.

We can use Conditional Formatting to quickly highlight the different values in two Excel sheets.

Note: This method cannot compare two sheets in different workbooks.

Therefore, we need to integrate Book1 and Book2 into one workbook.

The following are the specific steps of how to compare two excel sheets and highlight differences.

1. In the table, select the values that need to highlight the differences, here we select the data in sheet2. 

2. Click the Home tab, select Conditional Formatting, and add New Rule. 

3. In the Select a rule type dialog box, click Use a Formula to determine which cells to format. 

4. In the formula bar, enter = A2  Sheet1!A2.

In this formula, “A2” indicates cell A2 of this worksheet；

“Sheet1!A2” indicates the reference to cell A2 in Sheet1；

is an unequal sign.；

This formula means that in the selected range of data, the data in Sheet2 will  compare that of Sheet1 one by one and highlight the different cells.

5. Set the format, select the highlight color for the mismatched data, and confirm. 

6. Click OK, at this time the differences between the two worksheets values will be immediately highlighted. 

Note:

When we use the formulas, only relative references can be entered. For example, enter a cell as A1 instead of $A$1 or $A1 or A$.  

Click here to know more about  relative references  and “absolute references”.

This formula can only be used to compare differences in values. 

This method can only be applied in two sheets in the same Excel document. 

What is Worksheet in WPS Spreadsheet？

The worksheet used in Excel documents is a collection of cells organized by rows and columns.  It is the working surface you interact with to enter data. Each worksheet contains 1048576 rows and 16384 columns and can be used as a giant table to organize information. Same as Microsoft Office Excel.

More advanced features of WPS Office Spreadsheet:

In addition to comparing the difference in values between two Excel sheets, Excel also has a Find and Replace feature that you can turn on to analyze workbooks, view cells, and remove redundant formatting from worksheets. And you can get answers from this article on how to find different projects in WPS Academy, Use data check method to quickly find different items.

WPS Spreadsheet for beginners course will help you learn Excel basics. Using free and professional WPS Spreadsheet to manage and analyze various types of data effectively. This free courses teach you basic to advanced concepts of Microsoft Excel.