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

Sometimes, we require 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.


Download Practice Workbook

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


7 Easy Ways to Compare Two Excel Sheets to Find Missing Data

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.

Using IF Function to Compare Two Excel Sheets to Find Missing Data

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

=IF('T-1'!B5 <> 'T-2'!B5, "T-1:"&'T-1'!B5&" vs T-2:"&'T-2'!B5, "")

  • Press Enter.

Using IF Function to Compare Two Excel Sheets to Find Missing Data

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

Using IF Function to Compare Two Excel Sheets to Find Missing Data

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

Using IF Function to Compare Two Excel Sheets to Find Missing Data

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 (6 Suitable Ways)


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 into the cell.

=VLOOKUP(B5,'T-2'!$B$4:$C$14,2,FALSE)

  • Press Enter.

Applying VLOOKUP Function to Compare Two Excel Sheets to Find Missing Data

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

Applying VLOOKUP Function to Compare Two Excel Sheets to Find Missing Data

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

πŸ”Ž Interpretation of the Result

Here, the data available in the range of cells B5:C14 is the value of sheet T-1. We use the VLOOKUP function, which will look for the Case ID of sheet T-1 and show the corresponding value of sheet T-2. As the cells C9 and C13 of sheets T-2 are blank, the formula returns us zero (0).

Read More: How to Compare Two Columns in Excel for Missing Values (4 ways)


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 into the cell.

=ISERROR(VLOOKUP(C5,'T-2'!$C$4:$C$14,1,FALSE))

  • Press Enter.

Utilizing ISERROR and VLOOKUP Functions to Compare Two Excel Sheets to Find Missing Data

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

Utilizing ISERROR and VLOOKUP Functions to Compare Two Excel Sheets to Find Missing Data

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

πŸ”Ž Interpretation of the Result

Here, the data available in the range of cells B5:C14 is the value of sheet T-1. The ISERROR function will check the value of the VLOOKUP function. When the value match, the ISERROR function returns FALSE. On the other hand, when there is a blank cell, the function returns TRUE.

πŸ”Ž Breakdown of the Formula

We are breaking down the formula for cell D9.

πŸ‘‰ VLOOKUP(C9,’T-2β€²!$C$4:$C$14,1,FALSE): The function will check the exact match of the cell value C5 in the sheet T-2. As the value is not available in sheet T-2 for the corresponding match, the function will return #N/A.

πŸ‘‰ ISERROR(VLOOKUP(C9,’T-2β€²!$C$4:$C$14,1,FALSE)): The ISERROR function will check whether the value of the VLOOKUP function is an error or not. For this cell, the result is not an error. Hence, it returns TRUE.

Read More: How to Filter Missing Data in Excel (4 Easy Methods)


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.

=IF(ISERROR(VLOOKUP(C5,'T-2'!$C$4:$C$14,1,FALSE)),C5,"")

  • Press Enter.

Combing IF, ISERROR and VLOOKUP Functions to Compare Two Excel Sheets to Find Missing Data

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

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

Combing IF, ISERROR and VLOOKUP Functions to Compare Two Excel Sheets to Find Missing Data

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

πŸ”Ž Breakdown of the Formula

We are breaking down the formula for cell D9.

πŸ‘‰ VLOOKUP(C9,’T-2β€²!$C$4:$C$14,1,FALSE): The function will check the exact match of the cell value C5 in the sheet T-2. As the value is not available in sheet T-2 for the corresponding match, the function will return #N/A.

πŸ‘‰ ISERROR(VLOOKUP(C9,’T-2β€²!$C$4:$C$14,1,FALSE)): The ISERROR function will check whether the value of the VLOOKUP function is an error or not. For this cell, the result is not an error. Hence, it returns TRUE.

πŸ‘‰ IF(ISERROR(VLOOKUP(C9,’T-2β€²!$C$4:$C$14,1,FALSE)),C9,””): The IF function checks the result of the ISERROR function. If the logic is true it will show the value of C9. Otherwise, it returns blank. In this cell, the function returns the value of cell C9, which is Hagrid.

Read More: How to Find Missing Values in a List in Excel (3 Easy Methods)


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.

=NOT(ISNUMBER(MATCH(C5,'T-2'!$C$4:$C$14,0)))

  • Then, press Enter.

Using NOT, ISNUMBER and MATCH Functions to Compare Two Excel Sheets to Find Missing Data

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

Using NOT, ISNUMBER and MATCH Functions to Compare Two Excel Sheets to Find Missing Data

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

πŸ”Ž Interpretation of the Result

Here, the data available in the range of cells B5:C14 is the value of sheet T-1. The combined formula of NOT, ISNUMBER, and MATCH functions returns FALSE when the cells of sheet T-2 contain data. But, when there is a blank cell, the formula returns TRUE.

πŸ”Ž Breakdown of the Formula

We are breaking down the formula for cell D9.

πŸ‘‰ MATCH(C9,’T-2β€²!$C$4:$C$14,0): The function will find the match of the value of cell C5 in the datasheet T-2. As the value is not available in sheet T-2, the function will return #N/A.

πŸ‘‰ ISNUMBER(MATCH(C9,’T-2β€²!$C$4:$C$14,0)): The ISNUMBER function will check whether the value of the match function is a number or not. For this cell, the result is not a number. Hence, it returns FALSE.

πŸ‘‰ NOT(ISNUMBER(MATCH(C9,’T-2β€²!$C$4:$C$14,0))): The NOT function checks the result as logic and it will toggle the result. For this cell, the formula returns TURE.

Read More: How to Interpolate Missing Data in Excel (4 Ways)


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.

Applying Arrange All Command to Compare Two Excel Sheets to Find Missing Data

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

Applying Arrange All Command to Compare Two Excel Sheets to Find Missing Data

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

Applying Arrange All Command to Compare Two Excel Sheets to Find Missing Data

  • 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 will show side by side, and you will be able to compare both sheets manually and find the missing data.

Applying Arrange All Command to Compare Two Excel Sheets to Find 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 (5 Useful Tricks)


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. If you don’t have that, you have to enable the Developer tab. Or You can also press β€˜Alt+F11’ for opening the Visual Basic Editor.

Embedding VBA Code to Compare Two Excel Sheets to Find Missing Data

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

Embedding VBA Code to Compare Two Excel Sheets to Find Missing Data

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

Embedding VBA Code to Compare Two Excel Sheets to Find Missing Data

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

Embedding VBA Code to Compare Two Excel Sheets to Find Missing Data

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


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.

Don’t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta

Soumik Dutta

Hi! I am Soumik. I have completed my Bachelor of Science in Naval Architecture and Marine Engineering. I have found my calling in Machine Learning and Data Science, and in pursuing so, I have realized the importance of Data Analysis. Excel is one excellent tool to fulfill the purpose. I am always trying to learn every day about different features of Excel and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo