How to Replace #N/A Values in Excel (With Examples)

You can use the following basic syntax to replace #N/A values in Excel with either zeros or blanks:

```#replace #N/A with zero
=IFERROR(FORMULA, "0")

#replace #N/A with blank
=IFERROR(FORMULA, "")
```

The following example shows how to use this syntax in practice to replace #N/A values from a VLOOKUP with zero or blanks.

Example: Replace #N/A Values in Excel

Suppose we have the following dataset in Excel:

And suppose we use the VLOOKUP() function to look up points based on team name:

Notice that some of the values returned in the VLOOKUP() are #N/A values.

We can turn these values into zeros by using the IFERROR() function as follows:

```#replace #N/A with zero
=IFERROR(VLOOKUP(A2, \$A\$1:\$B\$11, 2, FALSE), "0")```

The following screenshot shows how to use this function in practice:

Alternatively, we can turn the #N/A values into blanks using the IFERROR() function as follows:

```#replace #N/A with blank
=IFERROR(VLOOKUP(A2, \$A\$1:\$B\$11, 2, FALSE), "")```

The following screenshot shows how to use this function in practice:

Notice that each value that was previously #N/A is now blank.

Using the IFERROR() function, we can replace #N/A values with any value that we’d like.

In the previous examples, we simply chose to replace #N/A values with zeros or blanks because these are the most common replacement values used in practice.

4 Replies to “How to Replace #N/A Values in Excel (With Examples)”

1. Sue says:

Awesome, it worked for me right away!

1. James Carmichael says:

Great to know! Thank you, Sue for the update!

2. Robert Gomabon says:

Greetings are you able to help with setting up my spreadsheet with proper formula calculations?

1. James Carmichael says:

Hi Robert…What are some questions we can help with?