How to Compare Two Excel Sheets to Find Missing Data (7 Ways)

Sometimes, we need to compare two Excel sheets to find the missing data. We can complete the process in several ways. In this article, we are going to show seven ways to compare two Excel sheets to find missing data. If you are also curious about it, download our practice workbook and follow us.

How to Compare Two Excel Sheets to Find Missing Data: 7 Creative Ways

To demonstrate the approaches, we consider two employee data lists in two sheets titled T-1 and T-2.

Moreover, we have two blank cells in the data table of sheet T-2 at cells C9 and C13. Now, we will show you seven distinct methods to find them.

1. Using IF Function

In this method, we will use the IF function to compare two Excel sheets to find missing data. We have to use the formula in a new sheet. The steps of this process are given below:

📌 Steps:

• First of all, create a new sheet using the Plus (+) sign located in the Sheet Name Bar.

• Now, write down the following formula in cell B5.

• Press Enter.

• Then, drag the Fill Handle icon to your right to copy the formula up to cell C5.

• After that, select the range of cells B5:C5 and drag the Fill Handle icon to copy the formula up to cell C14.

• You will see in cells C9 and C13, that the formula will show us the cell value of sheet T-1 and the missing value of sheet T-2.

Thus, we can say that our formula worked perfectly, and we were able to compare two Excel sheets to find missing data.

Read More: How to Deal with Missing Data in Excel

2. Applying VLOOKUP Function

In this process, we are going to use the VLOOKUP function to compare two Excel sheets to find missing data. The steps of this approach are given as follows:

📌 Steps:

• First, select cell D5.
• After that, write down the following formula in the cell.

• Press Enter.

• Then, double-click on the Fill Handle icon to copy the formula up to cell D14.

• You will notice the formula will show the value of all cells except D9 and D13. In both cells, the function will show us Zero (0) for the missing value.

So, we can say that our formula worked effectively, and we were able to compare two Excel sheets to find missing data.

Read More: How to Filter Missing Data in Excel

3. Utilizing ISERROR and VLOOKUP Functions

In this approach, the ISERROR and VLOOKUP functions will help us to compare two Excel sheets to find missing data. The procedure of this method is shown below:

📌 Steps:

• At first, select cell D5.
• Now, write down the following formula in the cell.

• Press Enter.

• After that, double-click on the Fill Handle icon to copy the formula up to cell D14.

• You will get TRUE for cells D9 and D13. Whereas, all other cells will show FALSE.

Hence, we can say that our formula worked precisely, and we were able to compare two Excel sheets to find missing data.

Read More: How to Find Missing Values in Excel

4. Combing IF, ISERROR and VLOOKUP Functions

In this case, we are going to use a combined formula of the IF, ISERROR, and VLOOKUP functions to compare two Excel sheets to find missing data. The procedure of this case is explained below:

📌 Steps:

• Firstly, select cell D5 and write down the following formula into the cell.

• Press Enter.

• Afterward, double-click on the Fill Handle icon to copy the formula up to cell D14.

• You will see the missing values of cells C9 and C13 of sheet T-2, which are shown in the corresponding cells.

Therefore, we can say that our formula worked successfully, and we were able to compare two Excel sheets to find missing data.

Read More: How to Find Missing Values in a List in Excel

5. Using NOT, ISNUMBER and MATCH Functions

In this method, we will use another combined formula using the NOT, ISNUMBER, and MATCH functions to compare two Excel sheets to find missing data. The procedure of this method is described below:

📌 Steps:

• First of all, select cell D5.
• Next, write down the following formula into the cell.

• Then, press Enter.

• Now, double-click on the Fill Handle icon to copy the formula up to cell D14.

• You will get TRUE results for cells D9 and D13. Whereas all other cells will show FALSE.

At last, we can say that our formula worked perfectly, and we were able to compare two Excel sheets to find missing data.

Read More: How to Cross Reference in Excel to Find Missing Data

6. Applying Arrange All Command from View Tab

In this approach, we will use the Arrange All command to compare two Excel sheets manually to find missing data. The steps of this process are given below:

📌 Steps:

• At first, in the View tab, select the New Window command from the Window group to create a copy of our current workbook.

• As a result, a copy of our workbook will appear.

• Now, in the View tab, select the Arrange All command.

• A small dialog box called Arrange Window will appear.
• Then, change the Arrange field option from Tiled to Vertical.
• Finally, click OK.

• You will see both workbooks side by side, and you will be able to compare both sheets manually and find the missing data.

In the end, we can say that our procedure worked effectively, and we were able to compare two Excel sheets to find missing data.

Read More: How to Fill Missing Values in Excel

7. Embedding VBA Code

Writing a VBA code can also help you to compare two Excel sheets to find missing data. For showing this method, we are using our previous datasheets which we have already used. The steps of this process are given as follows:

📌 Steps:

• To start the approach, go to the Developer tab and click on Visual Basic. You can also press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• Now, in the Insert tab on that box, click the Module option.

• Then, write down the following visual code in that empty editor box.

Sub Compare_Two_Sheets()
Dim Rng_Cell As Range
For Each Rng_Cell In Worksheets("T-1").Range("B4:C14")
    If Not Rng_Cell = Worksheets("T-2").Cells(Rng_Cell.Row, Rng_Cell.Column) Then
        Rng_Cell.Interior.Color = vbYellow
    End If
Next Rng_Cell
End Sub

• Afterward, press “Ctrl+S’ to save the code.
• Close the Editor tab.
• After that, in the Developer tab, click on Macros.

• A new dialog box called Macro will appear.
• Select Compare_Two_Sheets and click on the Run button to run this code.

• Finally, you will see the corresponding cells of the missing value of sheet T-2 shown in highlight form.

Finally, we can say that our visual code worked successfully and we were able to compare two Excel sheets to find missing data.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to compare two Excel sheets to find missing data. Please share any further queries or recommendations with us in the comments section below if you have any other questions or recommendations.

Keep learning new methods and keep growing!

Related Articles

• How to Find Missing Rows in Excel
• Count Missing Values in Excel
• How to Remove Missing Values in Excel

<< Go Back To Missing Values in Excel | Data Cleaning in Excel | Learn Excel