How to conditional format cells if containing #N/A in Excel?
With the conditional formatting function, you can quickly format all cells which containing #N/A in Excel. This tutorial will show you how to achieve it in details.
Recommended Productivity Tools by Us
Please do as follows to format cells which containing #N/A with conditional formatting function.
1. Select the range which contains the #N/A cells you want to format.
2. Then click Conditional Formatting > New Rule under Home tab. See screenshot:
3. In the New Formatting Rule dialog box, please do as follows.
1). Select Use a formula to determine which cells to format in the Select a Rule Type box;
2). Copy and type formula =ISNA(A1) into the Format values where this formula is true box;
Note: A1 in the formula is the beginning cell of my selected cell range, you need to change it to meet your selection. If your selected range is A2:D10, the A1 should be changed to A2 in the formula.
3). Click the Format button. See screenshot:
4. In the Format Cells dialog box, specify format for the #N/A cells, and then click the OK button.
5. When it returns to the New Formatting Rule dialog box, click the OK button.
You can see all cells which contain #N/A are formatted with the format you specified above.
- How to conditional format cells based on first letter/character in Excel?
- How to conditional format dates less than/greater than today in Excel?
- How to conditional format or highlight first recurrence in Excel?
- How to conditional format negative percentage in red in Excel?
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...