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. 

Compare and Highlight the Difference between Excel Files

The time and effort it takes to compare excel files can be a lot to handle. If you don’t want to use any formulas or formatting patterns, you should consider using any of these two tools discussed below. 

1. XLComparator 

The good thing about the XLComparator tool is that it provides you with a lot of comparison features. You can decide which things to look for and which ones to ignore when you are comparing two files. 

2. CloudyExcel

CloudyExcel is another tool you can use to compare excel files. The good thing about this tool is its simplicity. With this tool, you are sure to find what you are looking for in an excel sheet in no time. 

FAQs

Q1. Can I compare Excel files on different operating systems?

A. You can compare excel files on various operating systems. However, you will have to ensure that the operating system you are going to use can support files that handle .xlsx format. For example, you can use WPS Office on Windows, Mac, and Linux to compare files. 

Q2. What should I do if the compared files contain confidential information?

A. The compare excel files compromise the safety of your data. This is why you need to be careful about using this feature. Here are some tips to help you protect your data when comparing files: 

• Remove any sensitive data from your file. 

• Send your files over encrypted file servers. 

• Make sure you encrypt your files for safety. 

• Only allow authorized people to access your files. 

• Protect your files using secure protection methods. 

• Delete any files that you no longer use for individual or professional needs. 

Q3. Can I compare Excel files with different versions of Microsoft Excel?

A. It is possible to compare excel files in different versions of Microsoft Excel. However, if you want to avoid comparison issues, you should consider using the most updated version of Excel to files easily. 

Compare Files and More with WPS Office

The compare excel files feature can make things easier if you have to deal with a lot of data. However, if you don’t want to work with lengthy formulas or don’t like the interface of Microsoft Excel, you should consider using WPS Office. 

The good thing about WPS Office is that it helps you create spreadsheets, documents, slides, and PDFs in one place. You can use this powerful software on leading platforms, including iOS, Android, Linux, Mac, and Windows. 

Getting started with WPS Office is easier compared to other tools. For example, unlike Microsoft Office, you don’t need to buy a plan to use WPS Office. The user-friendly interface of WPS Office makes it a perfect choice for your personal and professional needs.