When a formula finds or it calculates correctly, it will show the good result but if it doesn’t or it is wrongly calculated, the formula will display an error like #N/A, #REF!, #DIV/0, etc.

 

When I use the formula ?

Each time I get an error because the formula is not able to display the correct results so I want to show something else like an empty cell, “no data found” or anything I want.

 

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

 

How are the formulas ?

According to the error I get based on the data, there are 4 formulas I usually use:

=IFERROR()

Put this formula:

=IFERROR(SUM(E2:E5),"no data")

=IFERROR(SUM(E2:E5),"")

=IFERROR(GETPIVOTDATA("Windows",pivotable!$A$2,"Country","France"),"0")

I am asking to sum different cells, so if it returns an error, I ask it to put “no data” (first formula), an empty cell (second formula) or “0” (third formula).

=ISERROR()

Putting the formula in sheet2:

=IF(ISERROR(GETPIVOTDATA("Windows",pivotable!$A$2,"Country","France")),"no data",GETPIVOTDATA("Windows",pivotable!$A$2,"Country","France"))

=IF(ISERROR(GETPIVOTDATA("Windows",pivotable!$A$2,"Country","France")),"",GETPIVOTDATA("Windows",pivotable!$A$2,"Country","France"))

To display what I want, I need to combine with “IF” so if the cell is showing an error, I ask it to put “no data” (first formula) or an empty cell (second formula). This one works the same as “IFERROR” but “ISERROR” works better for a pivot table.

=ISNA()

Put this formula:

=IF(ISNA(E4),"no data",E4)

=IF(ISNA(E4),"",E4)

If the cell is showing an error, I ask it to put “no data” (first formula) or an empty cell (second formula)

=IF()

Put this formula:

=IF(E5=0,"no data",E5)

=IF(E5=0,"",E5)

If the cell contains 0, I ask it to put “no data”(first formula) or an empty cell (second formula)