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

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.


Download Practice Workbook

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


4 Ways to Filter Missing Data in Excel

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 (3 Easy Ways)


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

The same previous operation we can perform using the IF,ISNA, and MATCH 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 (6 Suitable Ways)


Similar Readings


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 (3 Easy Methods)


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 (5 Useful Tricks)


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. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

ExcelDemy
Logo