Yet Another Blank Cell Solution

• 2 mins read

In the image below the list on the left has some blank cells. The FILTER function in cell F3 displays those blank cells as zeroes.

In the Amount column of the FILTER function two zeroes are displayed, but there is only one zero in the original data.

To fix the issue you can use the LET function and an IF function to show blanks as blanks.

The amended formula is shown below.

The LET function captures the filtered data set as the d variable. The IF function then looks at each cell in the filtered list. If the cell is blank then a blank cell is returned otherwise the entry is returned.

This image above shows the Amount column with a blank cell and a zero cell.

The downside of this is that the cells showing as blank in the filtered list have the text entry “” so if used in a calculation may return the #VALUE error – see example below. The formula in cell K3 has been copied down.

If this is an issue you can use the N function to handle text entries – see image below. The N function in cell L3 converts text entries to zero and leave numbers unchanged.