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 (row 2, 3 and 5):

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

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

=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()

Put this formula (row 2, 3 and 5):

=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.

=IFNA()

Put this formula (row 4):

=IFNA(E4,"no data")

=IFNA(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 (row 6):

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

=IF(E6=0,"",E6)

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