How to Format cells containing #N/A Error in Excel

This post will guide you how to format cells containing #N/A Error in Excel. How do I add conditional formatting to cells that containing #N/A Error in Excel 2013/2016/2019/365.

The #N/A error often occurs when a lookup or reference function cannot find a matching value. By formatting cells with the #N/A error, you can visually identify the missing or invalid data points.

1. Format cells containing #N/A Error

Assuming that you have a range of cells which contain #N/A errors, and you want to use conditional formatting to highlight cells which contain #N/A error. How to do it. Just do the following steps:

As #N/A is not a text, you need to use ISNA function to match on an error of this type in the Conditional Formatting.

Step1: select the range of cells that contain #N/A errors and you want to highlight.

format cells contain na1

Step2: go to Home Tab, and click Conditional Formatting command under Styles group. Then click New Rule from the popup menu list. And the New Format Rule dialog will open.

format cells contain na2

Step3: select use a formula to determine which cells to format in the Select a Rule Type list box, and enter the following formula in the text box of Format values where this formula is true:

format cells contain na3

Note: Cell A1 is the first cell that you select.

Step4: click Format button, and the Format Cells dialog will open. Switch to Fill tab in the Format Cells dialog , and choose one color as background color. And click Ok button.

format cells contain na4

Step5: then returns to the new Formatting Rule dialog box, click Ok button again.

Step6: You would see that all cells which contain #N/A error have been highlighted with green color as you set.

format cells contain na5

Now, any cells within the selected range that contain the #N/A error will be formatted according to the formatting options you specified in the Format Cells dialog box.

2. Video: Format cells containing #N/A Error

This video will show you how to format cells containing the #N/A error using Conditional formatting feature in Excel.

Leave a Reply