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

Get FREE Advanced Excel Exercises with Solutions!

Filtering for a value in a list, whether missing or not, is too common in Excel. But the Excel filter command can’t do it, we need to use formulas for it. This article will provide you with 4 easy methods to filter missing data in Excel.


How to Filter Missing Data in Excel: 4 Easy Ways

Get introduced to our dataset first that represents some salesperson’s sales regions.


1. Using IF and COUNTIF Functions to Filter Missing Data

In our very first method, we’ll apply the IF and COUNTIF functions to filter missing values from a list. If the value is found then it will return “Found” and if not then it will return “Missing”.

Steps:

  • Activate Cell C14 and insert the following formula into it-
=IF(COUNTIF(C5:C11,B14),"Found","Missing")
  • Then just hit the ENTER button to get the output.

Using IF and COUNTIF Functions to Filter Missing Data

Formula Breakdown:

  • COUNTIF(C5:C11,B14)

The COUNTIF function will count the number of filtering values along the range C5:C11.

  • IF(COUNTIF(C5:C11,B14),”Found”,”Missing”)

Then the IF function will return “Missing” for 0 and “Found” for any number greater than zero.

  • After that, drag down the Fill Handle icon to copy the formula for the other cells.

Using IF and COUNTIF Functions to Filter Missing Data

Now see, we were filtering for three values and found 1 value and two are missing.

Using IF and COUNTIF Functions to Filter Missing Data

Read More: How to Find Missing Values in Excel


2. Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

The same previous operation can be performed using the IF, ISNA, andMATCH functions. It will give us the same output as before. You can use the ISERROR function instead of the ISNA function too, it will result same.

Steps:

  • Insert the following formula in Cell C14
=IF(ISNA(MATCH(B14,$C$5:$C$11,0)),"Missing","Found")
  • Later, press the ENTER button to get the output for the result.

Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

Formula Breakdown:

  • MATCH(B14,$C$5:$C$11,0)

The MATCH function will return the row number from the list if the value matches and will return the #N/A error if the value doesn’t match.

  • ISNA(MATCH(B14,$C$5:$C$11,0))

Next, the ISNA function will return TRUE for #N/A error and FALSE for any other output.

  • IF(ISNA(MATCH(B14,$C$5:$C$11,0)),”Missing”,”Found”)

Finally, the IF function will return Missing for TRUE and Found for False.

  • Next, use the Fill Handle tool to copy the formula.

Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

And have a look, we got the same output as before.

Read More: How to Deal with Missing Data in Excel


3. Filter Missing Data Using IF, ISNA, and VLOOKUP Functions

If we use the VLOOKUP function instead of the MATCH function in the previous formula then we’ll get the same result. Because the MATCH and VLOOKUP functions work pretty like the same.

Steps:

  • In Cell C14, apply the following formula-
=IF(ISNA(VLOOKUP(B14,$C$5:$C$11,0)),"Missing","Found")
  • Finally, just press the ENTER button.

Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

Formula Breakdown:

  • VLOOKUP(B14,$C$5:$C$11,1)

Here, the VLOOKUP function will return the lookup value if it matches and will return the #N/A error if doesn’t match.

  • Lastly, apply the Fill Handle tool to insert the same formula for the other regions.

Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

Soon you will get the output like the image below.

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


4. Using TEXTJOIN, FILTER, and COUNTIF Functions to Filter Missing Data by Comparing Two List

In the previous methods, we filtered values only from a list. Now we’ll learn a way to filter data from two lists to filter one list’s data to another list using the TEXTJOIN, FILTER, and COUNTIF functions. This formula will directly return the missing region names. For this method, we modified the dataset and used two columns to show the selling regions for two years.

Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel

Steps:

  • To filter for the regions of 2020 in 2021, write the following formula in Cell B14
=TEXTJOIN(", ",TRUE,FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0))
  • After that, hit the ENTER button and it will return region names. The formula returned Canada two times because Canada remained two times in 2020 and this formula doesn’t merge the same values.

Formula Breakdown:

  • COUNTIF(D5:D11,C5:C11)=0

First, the COUNTIF function will count the number for each value of the range C5:C11 through the range D5:D11 and the output will return as an array. If any value gets equal to zero then it will return TRUE and if not then will return FALSE.

  • FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0)

Later, the FILTER function will return the value from the range C5:C11 if it matches the criteria.

  • TEXTJOIN(“, “,TRUE,FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0))

Finally, the TEXTJOIN will join the outputs using a comma.

  • Then to filter for the regions of 2021 in 2020, insert the following formula in Cell B15
=TEXTJOIN(", ",TRUE,FILTER(D5:D11,COUNTIF(C5:C11,D5:D11)=0))
  • Finally, just press ENTER.

This formula performs the same operation as the previous one. Hope you’ve understood that, if not feel free to comment I’ll try to help then.

Read More: How to Fill Missing Values in Excel


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the procedures described above will be good enough to filter missing data in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo