How to Find, Count & Fill Missing Values in Excel?

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

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

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.

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.

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.

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.

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 IDs and another range E5:E8 that’s showing the available product IDs 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 IDs from the range B4:B12 that aren’t mentioned in column E.

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.

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.

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

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

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