How to Replace #N/A Values in Excel

In this tutorial, you will learn how to replace #N/A values in Excel.

The error code #N/A indicates that “no value is available.” To designate blank cells, use NA. To prevent accidentally including empty cells in your calculations, enter #N/A in the cells where you are missing information.

We can easily replace #N/A with something else by using the IFERROR function.

Once ready, we’ll get started by utilizing real-world examples to show you how to replace #N/A values in Excel.

Anatomy of IFERROR Function

IFERROR(value, value_if_error)

If a formula evaluates to an error, IFERROR returns a value you define; if not, it returns the formula’s result.

Replace #N/A Values

Before we begin we will need a group of data to replace #N/A values in Excel.

Step 1

First, you need to have a clean and tidy group of data to work with.

Step 2

To replace #N/A with “Absent”, we need to insert the following formula =IFERROR(VLOOKUP(D2, $A$1:$B$6, 2, FALSE), “Absent”).

Step 3

Once you are done, you will be able to replace #N/A values in Excel.