How to Use the ISNA Function in Excel
Today, we will explore the ISNA function in Excel and Google Sheets.
Overview
The ISNA function is utilized to determine if a cell contains the #N/A error value. It returns TRUE if the cell’s value is #N/A; otherwise, it returns FALSE. This function is straightforward and proves extremely useful in managing datasets that might include errors.
Syntax
The syntax for the ISNA function is identical in both Excel and Google Sheets:
ISNA(value)value– The value you want to test for the #N/A error.
Examples
Example 1: Basic Usage
Assume cell A1 contains the #N/A error. Using the ISNA function to check this error would work as follows:
| A | B |
|---|---|
| #N/A | =ISNA(A1) |
In cell B1, the formula =ISNA(A1) will return TRUE.
Example 2: Conditional Formatting
The ISNA function can also be utilized with conditional formatting to visually highlight cells that contain #N/A errors. Here’s how to apply it:
- Select the range of cells you wish to format.
- Go to “Format” – “Conditional formatting” in the toolbar.
- Select “Custom formula is” from the drop-down menu.
- Input the formula
=ISNA(A1)(assuming A1 is the top-left cell in your selected range). - Choose the formatting style you prefer for cells containing #N/A errors.
Example 3: Using IF Function
The ISNA function can be paired with the IF function to execute specific actions depending on the presence of a #N/A error in a cell. Consider the following example:
| A | B |
|---|---|
| #N/A | =IF(ISNA(A1), “Error found”, “No error”) |
In cell B1, the formula =IF(ISNA(A1), "Error found", "No error") will yield “Error found.”
These examples highlight the versatility of the ISNA function in Excel and Google Sheets. Whether you are checking for errors, highlighting problematic cells, or integrating error-checking into your formulas, the ISNA function is an essential tool for your data management toolkit.
More information: https://support.microsoft.com/en-us/office/is-functions-0f2d7971-6019-40a0-a171-f2d869135665