In this Excel tutorial, you will learn how to

– Use several built-in Excel functions to find missing values.

– Compare two lists for missing values.

– Fill missing values.

– Count missing values.

– Analyze missing values.

We have used Microsoft 365 while preparing this article but the methods here are also applicable for the other versions as well.

To get an accurate output, we often need to find, count, and fill missing data in Excel. Excel has several amazing built-in features that allow you to handle missing values in an efficient and precise way. Some of these functions are the **IF**, **COUNTIF**, **ISNA**, **VLOOKUP**, and **ISNUMBER **functions.

**Download Practice Workbook**

**Table of Contents**Expand

## How to Find Missing Values in Excel?

In Excel, we can find missing values

– Using the combination of **IF** and **COUNTIF** functions

– Utilizing the combination of **IF**, **ISNA**, and **MATCH** functions

– Merging **IF**, **ISNA**, and **VLOOKUP** functions or

– Using the combination of **IF**, **ISNUMBER**, and **MATCH** functions.

We will be using the following dataset as an example to illustrate all the methods to find missing values in Excel. The dataset represents some products with their product **ID**s.

### 1. Find Missing Values Using a Combination of IF and COUNTIF Functions

Using the combination of Excel’s built-in **IF** and **COUNTIF** functions, you can easily find all the missing values.

The **IF** function allows you to perform logical tests and returns different values such as true and false based on given criteria. Have a look at the brief overview of this function.

The **COUNTIF** function is used to count the number of cells within an array that meet a specific criterion. The following image shows several examples of the use of the **COUNTIF** function in Excel.

Now, we will be using these two functions together within our dataset to find missing values.

- Select cell
**F5**. - Apply the formula below and press
**Enter**. - After that, drag down the
**Fill handle**icon.

`=IF(COUNTIF($B$5:$B$12,E5),"Available","Not Available")`

__Formula Breakdown__

**COUNTIF($B$5:$B$12,E5)**

This function looks for the value in cell **E5** within the range **B5:B12**. If the value in cell **E5** exists within the range **B5:B12**, then it returns **1**. If the value doesn’t exist within the range, then it returns **0**.

**IF(COUNTIF($B$5:$B$12,E5),”Available”,”Not Available”)**

The **IF** function returns **Available **if the **COUNTIF **function returns **1**. Otherwise, it returns **Not Available**.

### 2. Find Missing Values by Combining IF, ISNA, and MATCH Functions

You can find missing values with the combination of Excel’s built-in **IF**, **ISNA** and **MATCH** functions.

In Excel, the **ISNA** function is used to determine whether a given value or formula result results in the **#N/A** error. The **#N/A** error code in Excel is a frequent error value that means “**Not Available**” or “**Not Applicable**“. The **#N/A** error is returned by the **ISNA** function as **TRUE** if the supplied value is the error and **FALSE** otherwise. Let’s have a look at the overview of this function.

Excel’s **MATCH** function can be used to find a specific value’s location in a range or an array. It is frequently used to perform searches and determine the location of a value’s relative position within a dataset. The relative position of the matched value inside the range is returned by the **MATCH** function and can be utilized for a variety of things. The following image shows an overview of this function.

Now, we will show you how to combine **IF**, **ISNA**, and **MATCH** functions to find missing values in Excel.

- Select cell
**F5**, insert the formula below, and press**Enter**. - After that, drag down using the
**Fill Handle**icon.

`=IF(ISNA(MATCH(E5,$B$5:$B$12,0)),"Not Available","Available")`

__Formula Breakdown__

**MATCH(E5,$B$5:$B$12,0)**

This function finds the position of the value in cell **E5 **within the range **B5:B12**. If the value exists within the range, then the function returns that value’s position. When the value doesn’t exist within the range, the function returns **#N/A**.

**ISNA(MATCH(E5,$B$5:$B$12,0))**

If the **MATCH** function returns the position then the **ISNA** function returns **FALSE**. When the **MATCH **function returns **#N/A** then the **ISNA **function returns **TRUE**.

**IF(ISNA(MATCH(E5,$B$5:$B$12,0)),”Not Available”,”Available”)**

If the **ISNA** function returns **FALSE**, then the **IF **function returns **Available**. Other than that, it returns **Not Available**.

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

### 3. Search Missing Values by Merging IF, ISNA, and VLOOKUP Functions

Excel’s built-in **IF**, **ISNA**, and **VLOOKUP** functions can be used to find missing values quickly.

We have already seen the overview of the **IF** and the **ISNA **functions. Let’s get familiar with the **VLOOKUP** function. Excel’s **VLOOKUP** function is used for carrying out a vertical lookup in a table or range. It enables you to look up a value in the table’s leftmost column and then obtain that value from a different column. The term **vertical lookup** has been shortened to **VLOOKUP**.

Now, you will learn how one can use these functions to find missing values in Excel.

- Select cell
**F5**, insert the formula below, and press**Enter**. - Then use the
**Fill handle**to drag down.

`=IF(ISNA(VLOOKUP(E5,$B$5:$B$12,1,FALSE)),"Not Available","Available")`

__Formula Breakdown__

**VLOOKUP(E5,$B$5:$B$12,1,FALSE)**

The **VLOOKUP **function looks for the corresponding value of cell **E5** within the range **B5:B12**. If the corresponding value exists, then the function returns that value. Otherwise, the function returns **#N/A**.

**ISNA(VLOOKUP(E5,$B$5:$B$12,1,FALSE))**

If the **VLOOKUP **function returns any corresponding value, then the **ISNA **function returns **FALSE**. When the **VLOOKUP **function returns **#N/A**, then the **ISNA **function returns **TRUE**.

**IF(ISNA(VLOOKUP(E5,$B$5:$B$12,1,FALSE)),”Not Available”,”Available”)**

If the **ISNA** function returns **FALSE** then the **IF **function returns **Available**. If the **ISNA **function returns **TRUE **then the **IF **function returns **Not Available**.

### 4. Combining IF, ISNUMBER and MATCH Functions

By using Excel’s built-in **IF**, **ISNUMBER**, and **MATCH **functions together, you can quickly find missing values.

Excel’s **ISNUMBER** function can be used to determine whether a given value is a numeric number or not. It returns either **TRUE** if the value is a number or **FALSE **if it is not. Here’s a brief overview of its uses:

We will now show you how to use the combination of the **IF**, **ISNUMBER** and **MATCH** functions to find missing values in Excel.

- Activate cell
**F5**. - Apply the formula below and press
**Enter**. - Drag down using the
**Fill handle**icon.

`=IF(ISNUMBER(MATCH(E5,$B$5:$B$12,0)),"Available","Not Available")`

__Formula Breakdown__

**MATCH(E5,$B$5:$B$12,0)**

This function finds the position of the value in cell **E5 **within the range **B5:B12**. If the value exists within the range, then the function returns that value’s position. When doesn’t exist within the range, the function returns **#N/A**.

**ISNUMBER(MATCH(E5,$B$5:$B$12,0))**

If the **MATCH **function returns the position, then the **ISNUMBER **function returns **TRUE**. Otherwise, it returns **FALSE **if the **MATCH **function returns **#N/A**.

**IF(ISNUMBER(MATCH(E5,$B$5:$B$12,0)),”Available”,”Not Available”)**

Finally, for the **TRUE** statement returned by the **ISNUMBER **function, the **IF **function returns **Available**. And for the **FALSE **statement, it returns **Not Available**.

## How to Compare Two Lists for Missing Values in Excel?

With the use of Excel’s **FILTER** and **COUNTIF **functions together, you can easily get the desired result. We have already become familiar with the **COUNTIF **function. Excel’s **FILTER** function is used to extract particular data from a range according to predefined standards or restrictions. You can use it to build dynamic groups of data that satisfy particular criteria. Here is a quick summary of its applications:

Let’s say, you have a range **B4:B12** containing several product **ID**s and another range **E5:E8** that’s showing the available product **ID**s only from the range **B4:B12**. Now, we want to determine those products from the range **B4:B12** that are not available.

- Select cell
**F5**. - Type down the formula below and press
**Enter**. - Following this will return all the product
**ID**s from the range**B4:B12**that aren’t mentioned in column**E**.

`=FILTER(B5:B12,COUNTIF(E5:E8,B5:B12)=0)`

__Formula Breakdown__

**COUNTIF(E5:E8,B5:B12)**

First, the **COUNTIF** function goes through the range **B5:B12** and returns **1** if any value in range **B5:B12** exists in range **E5:E8**. If not, then it returns **0**.

**FILTER(B5:B12,COUNTIF(E5:E8,B5:B12)=0)**

The **FILTER **function returns those values from range **B5:B12** for which the **COUNTIF **function returned **0**.

## How to Count Missing Values in Excel?

Using Excel’s built-in **SUMPRODUCT** and **COUNTIF** functions, you can count the missing values just within a blink of an eye. With Excel’s **SUMPRODUCT** function, you can perform operations on related elements of various arrays and then sum the outcomes. This function is used for multidimensional calculations. It is frequently used to do complex array calculations, calculate totals depending on various criteria, and compute weighted averages. Have a look at the overview of its uses:

Now, we will show you how to use **SUMPRODUCT **and **COUNTIF** functions to count missing values in Excel.

Suppose, you have a dataset where the range **B5:B12 **contains some products’ ID numbers. And range **D5:D7** shows the available product IDs. We want to calculate the total number of products that are in range **B5:B12** but missing in range **D5:D7**.

- Select cell
**D14**. - Copy and paste the formula below and press
**Enter**.

`=SUMPRODUCT(--(COUNTIF(D5:D7,B5:B12)=0))`

__Formula Breakdown__

**COUNTIF(D5:D7,B5:B12)**

First, the **COUNTIF** function goes through the range **B5:B12** and returns **1** if any value is found in the range **B5:B12** and range **E5:E8**. If not, then it returns **0**.

**SUMPRODUCT(–(COUNTIF(D5:D7,B5:B12)=0))**

The **SUMPRODUCT **function will return the sum of the **0** returned by the **COUNTIF **function.

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

## How to Fill Missing Data in Excel?

Presume, you have a dataset as follows that represents some city’s total sales. But some cities’ total sales value is missing. So now, we want to fill those missing values with trending values using Excel’s built-in **Fill Series **feature.

- First, select range
**D5:D7**. - Then, follow these steps:
**Home****>> Editing****>> Fill >> Series**.

- A dialog box will appear on your screen as shown below.
- Mark
**Columns**,**Growth**, and**Trend**. - Click on
**OK**.

- After following the previous steps, you will find the result as follows.

- Following a similar procedure for the rest of the cities will return you the output as follows.

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

## How to Analyze Missing Data Using Excel Chart?

We will be using **the COUNTIFS function** to perform this operation. The **COUNTIFS** function is a built-in Excel tool that you can use to count values based on multiple criteria. Take a look at this function’s summary:

Let’s say, we have a dataset as follows that represents a shop’s daily total sales record for the year **2022**. We can see that total sales records are not available (**NA) **for some days. So we consider the total sales values of these days as missing data.

- Select cell
**H5**. - Apply the formula below and press
**Enter**. - Drag down using the
**Fill handle**.

`=COUNTIFS($B$5:$B$369,F5,$D$5:$D$369,"NA")/G5`

- After that, select cell
**I5**. - Insert the formula below and press
**Enter**. - Use the
**Fill handle**to drag down.

`=1-H5`

- Select range
**H4:I16**. - Go through these steps:
**Insert****>> Insert Column or Bar Chart****>> 100% Stacked Bar**.

- Following this will return you a chart as follows.

In the chart, you can see that the missing data is represented with blue color, and the available data is represented with orange color. The chart shows the percentage of missing and available data for each month.

## Frequently Asked Questions

**1. How do I fix missing cells in Excel?**

We can easily fix missing cells in Excel. Follow the steps below.

- Select the whole worksheet by using keyboard shortcuts
**Ctrl+A**. - From the
**Home**tab in the toolbar, click on the**Format**drop-down option. - Go to the
**Hide & Unhide**section.

From there, you can easily unhide cells along rows or columns and fix the missing cells as well.

**2. What are the missing values in Excel?**

In Excel, the term “missing values” refers to data that is missing from certain cells or columns. Usually, they appear as blank cells, cells with a dash (-), or cells with **NA** or **#N/A** errors.

**3. How do I replace missing values with zeros?**

You can use Excel’s built-in **Find and Replace **feature or the combination of **IF **and **ISBLANK **functions to replace missing values in Excel.

Let’s say, you have data along the range **A1:A10**. Some of the cells in this range are missing values. So to replace those missing values, select an empty cell and apply this formula:

`=IF(ISBLANK(A1:A10), 0, A1:A10)`

## Conclusion

Excel is extremely essential nowadays for data analysis and management. While working with a large amount of data, you may end up dealing with missing values.

We have already seen the use of Excel’s several built-in features to deal with missing values precisely, quickly, and efficiently. We have also incorporated different functions to create formulas that return missing values in different circumstances.

Hope, you have found what you were looking for. Visit our site **ExcelDemy** to find more relevant articles.

## Missing Values in Excel: Knowledge Hub

- How to Deal with Missing Data in Excel
- How to Filter Missing Data in Excel
- How to Find Missing Values in a List in Excel
- How to Compare Two Excel Sheets to Find Missing Data
- How to Cross Reference in Excel to Find Missing Data
- How to Find Missing Rows in Excel
- How to Remove Missing Values in Excel

**<< Go Back To Data Cleaning in Excel | Learn Excel**