Hiding Zeros When Using The FILTER Function In Excel

I recently had a request from an individual asking how to get rid of the zeros when using the FILTER function and it returns a zero instead of a blank. In this tutorial we are going to look at three options to accomplish this.

To download the file and follow along, go to the link on my website at:

Do you need help with a formula or an Excel project?
Including a sample file is always helpful!

You can donate to my channel through any of the following:

PayPal:

Zelle or Venmo:

[adrianhohy]

Hi Mike, you have been a great help! I’ve been scratching my head for the longest time. Thanks a lot for sharing.

[krishnasayeee]

Hi, is there any way to show blanks as blanks or blanks as "NA"/specified text in the Choosecols function?

[wayneedmondson]

Hi Mike. Clever options! Thanks for sharing. Thumbs up!!

[thanez]

Thank you! This helped me get the solution to a slightly different problem I was having. I'm posting here because googling my problem got me here. My list was a list of temperatures where 0 is a valid entry that has importance and needs to be preserved. My problem was the opposite as yours, that empty cells needed to be conserved as empty, but they would be filtered as 0. The filter basically turned "I don't know, you need to go get a measurement!" into "0°C", which is a huge problem. I modified the IF formula in the video from IF(FILTER=0;"";FILTER) to IF(FILTER="";"Empty";FILTER), then added a NUMBERVALUE() around that to convert "Empty" to a value error, then added IFERROR(;"") around that. My final formula looked like this:
Usually I would have used SUBSTITUTE() to make it a bit simpler, but I needed to run the NUMBERVALUE() anyways to clean up some erroneous entries in the list, such as numbers preceded by ' or numbers formatted as text or unnecessary decimal points like 32, 00. These INPUT SINS were stopping me from using maths to consolidate temperature entries from two databases. And guess what NUMBERVALUE() does? It turns empty cells into 0. UGH. Turning empty cells to "empty", then converting them to errors, and then removing errors was my solution.

[rushdieskarous]

Thank you very much for this video! This problem was bothering me a lot! Much appreciated!!!!

[MrScarymaster]

Thanks!
Just wondering on how the 2 filter function with an IF formula performs on a large dataset. Have you tested it maybe?

[AndrewAhlfield]

Thank you for the quick inline explanation of the 4 format text categories! Will definitely be referencing this video in the future!

[Roso]

So because Microsoft is doing poor job, we have to use the same function twice .... I'm not surprised Excel is soooo slooow and fereeeing and crashing all the time.
The other hints are much better, but im strugling applying them accross an array of multiple data types.

[impartialx]

Thanks! Exactly what I needed - you helped a lot!

[ahmedemad]

Thank you so much for the brilliant solution.