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