Suppose you are dealing with two sets of similar data. Suddenly, you find that the second data range is almost similar to the first set of data, but some of the data are missing in the second data range. Occasionally, you might have to deal with this missing data. If you are facing problems dealing with missing data range and reading this article, then definitely you’re on the right track. In this article, I will show you how to deal with missing data in Excel.

**Table of Contents**hide

## How to Deal with Missing Data in Excel: 6 Creative Ways

In this section, you will find 6 suitable and effective ways to deal with missing data in Excel. Let’s check them now!

### 1. Using ISERROR and VLOOKUP Functions

Let’s say, we have got a dataset of some people registered for taking a vaccine, their relevant ID, and the person present on the day of taking the dose of the vaccine.

You can see that some persons are missing on the day of taking the vaccine and we want to find out the missing persons. Here we will use the **ISERROR**, **VLOOKUP** functions to deal with the missing data. In order to do so, proceed with the following steps.

**Steps:**

- First of all, create a column and apply the following formula to the selected cell.

`=ISERROR(VLOOKUP(C5,$D$5:$D$14,1,0))`

Here,

**C5**= the data to find**$D$5:$D$14**= Lookup array

**Formula Breakdown**

The** VLOOKUP function** considers **C5** as the **lookup_value** and **$D$5:$D$14** as **lookup array**, **1** as **col_index_num**, and **0** as **range lookup**.

The **ISERROR function** returns **TRUE** as it finds an error or **FALSE** where it doesn’t find an error. So, when the **LOOKUP** **function** doesn’t find a value and makes an error, the** ISERROR** function returns** TRUE**, otherwise **FALSE**.

- Then, use the
**Fill Handle**tool to**Autofill**the formula for the next cells and you will get the output for the missing data.

- Here, you can also apply
**Conditional Formatting**to highlight the missing data. For this, just select the column> go to**Conditional Formatting**> select**Highlight Cells Rules**> select**Equal to**> select the cell (i.e.**E6**)> choose**Fill Color**> click**OK**.

**Note**: You can also highlight data manually from the **Home **tab by changing **Text Color** or **Fill Color**.

**Read More: **How to Filter Missing Data in Excel

### 2. Using NOT, ISNUMBER, MATCH Functions

For our previous set of data, now we want to show the use of **NOT**, **ISNUMBER** and **MATCH** functions to deal with the missing data. For this, just follow the steps below.

**Steps:**

- Firstly, create a new column for the missing data and apply the following formula to the selected cell.

`=NOT(ISNUMBER(MATCH(C5,$D$5:$D$14,0)))`

Here,

**C5**= the data to find**$D$5:$D$14**= Lookup Array

**Formula Breakdown**

The **MATCH function** looks up **C5** in the **$D$5:$D$14** range which is the **lookup** **array**.

The cell value of **C5 **is missing in the **lookup array**.

The** ISNUMBER function** gives the output as **TRUE** or **FALSE**.

So,** ISNUMBER(MATCH(C5,$D$5:$D$14,0)) **returns **FALSE**.

Hence, **NOT function** turns this **FALSE** into **TRUE**.

- Then, drag the formula for the next cells to find missing values.

- Then, highlight the missing data with
**Conditional Formatting**like the last step of**Method 1**.

**Read More: **How to Fill Missing Values in Excel

### 3. Extract Missing Data Using IF, ISERROR and VLOOKUP Functions

In the previous two methods, we have just created output with **TRUE** for missing data and **FALSE** for existing data. But now, we want to extract the original missing data from the list of the data table. In order to do so, just proceed with the steps below.

**Steps:**

- Firstly, create a new column for the missing person and apply the following formula to the selected cell.

`=IF(ISERROR(VLOOKUP(C5,$D$5:$D$14,1,FALSE)),C5,"")`

Here,

**C5**= the data to find**$D$5:$D$14**= Lookup array

**Formula Breakdown**

**IF function **tests the logic and after validation of the logic, it returns **C5**, otherwise leaves blank (i.e. **“ ”**)

- Now, just drag the formula down and the column will show only the missing data here.

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

### 4. Applying Conditional Formatting

Here, we will now apply **Conditional Formatting** to find out the missing data for the same dataset. We won’t create a new column here, rather we will just mark the missing data from the existing column. So, let’s start the process like the one below.

**Steps:**

- First of all, select the two columns (i.e.
**Registered Person**and**Present Person**). Go to the**Home**tab> click**Conditional Formatting**> select**New Rule**.

- Then, the
**New Formatting Rule**dialogue box will open up. Choose**Format only unique or duplicate values**from the**Select a Rule Type**field and select**Unique**from the**Format All**field> click**Format**.

- Now, choose a
**Fill Color**from the**Format Cells**dialogue box> click**OK**.

- Here, click
**OK**on the**New Formatting Rule**box.

- Hence, the main column (i.e.
**Registered Person**) will compare itself to the compared column (i.e.**Present Person**) and highlight the cells that are missing data.

**Read More: **How to Compare Two Excel Sheets to Find Missing Data

### 5. Using IF Function

Let’s say, like the previous dataset, some persons have come to take the vaccine on **DAY-1** and some from the **DAY-1** along with some new ones have come to take the vaccine on **DAY-2**. So, some of the persons taken vaccines on **DAY-1** are missing on **DAY-2**.

We want to find the data of the persons from **DAY-1** who are missing on **DAY-2** by applying the **IF function**. So, proceed like the steps below.

**Steps:**

- At first, create a new column for the missing data and apply the following formula to the selected cell.

`=IF(B5=C5,"Present","Missing")`

Here,

**B5**= the data to find from**DAY-1****C5**= the data to be compared

- Then, drag the formula for the other cells to get the
**Missing**and the**Present**data.

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

### 6. Missing Data in Different Sheets

For the previous 5 methods, we have dealt with missing data from the same worksheet. Now, we will deal with data from different worksheets. Here, we have the **ID** and **Name** of the persons taking vaccines at **DAY-1 **on one sheet.

And we have also some information on **DAY-2** at another sheet.

We want to find out the person’s names from **DAY-2** that are missing on **DAY-1**. In order to do so, proceed like the steps below.

**Steps:**

- Firstly, create a new column for the missing persons and apply the following formula.

`=IF(ISERROR(VLOOKUP(C5,'DAY-1'!$C$5:$C$14,1,FALSE)),C5,"")`

Here,

**C5**= the data to find**$D$5:$D$14**= Lookup array

- Then drag the formula down and the cells will show only the missing data.

**Read More: **How to Find Missing Rows in Excel

**Download Practice Workbook**

You can download the practice book from the link below.

## Conclusion

In this article, I have tried to show you some methods of how to deal with missing data in Excel. I hope this article has shed some light on your way of dealing with missing data in an Excel workbook. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box. This will help me enrich my upcoming articles. Have a great day!