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.

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:

=ISNA(A1)

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

 

You might also like:

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar