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

`=IF('T-1'!B5 <> 'T-2'!B5, "T-1:"&'T-1'!B5&" vs T-2:"&'T-2'!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.

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

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

**🔎 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 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.

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

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

**🔎 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 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.

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

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

**🔎 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

### 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**.

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

**🔎 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 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**