Conditional Formatting in Excel with Formula Current Cell

Suppose the data range to conditionally format is A2:A10.

• Select first cell A2.
• From Home TAB, Click Conditional Formatting, Manage Rules, New Rule.
• Under use formula to determine which cell to format. In the field Format values where this formula is true, enter =ISNA($A2).
• Click Format to set the cell formatting, then select OK.
• In the Conditional Formatting Rules Manager, edit the range under Applies to set $A2:$A10.
• Select Apply then OK.

Using relative references to refer to the current cell

In a conditional formatting formula, you can refer to the current cell by using the relative form of its usual address. e.g. If you want to format cell B2, then you could use a formula like this:

=ISNA(B2)

Because you use a relative reference (B2 rather than $B$2), when you copy it to a different cell, the formula is adjusted to be relative to the new cell. So if you use the format painter to copy the conditional format to cell C3 (or just copy the whole of B2 there), then inspect C3 in the conditional formatting rules manager, then you will see that the formula has automatically updated to

=ISNA(C3)

This principle also applies to ranges, but is a little trickier to understand. For a range, the formula is input relative to the top-left cell, but is interpreted relative to each cell in turn. So if you select the range of cells from B2 to D4, and apply the formula =ISNA(B2), any cell in the range will be formatted if it contains #N/A, not just B2.

Relative references to other cells

The same applies to other relative references: if your conditional formatting formula has a relative reference to the cell to the right of the one you are formatting, and you copy that format somewhere else, then the format of the new cell will depend on the value to the right of the new cell.

Absolute references

You can also use absolute references ($A$1 style), if you want the format of lots of cells to depend on the value of one cell. Absolute references don't change when you apply the conditional format to other cells.

Semi-relative references

Using a mix of absolute and relative references can be very useful for formatting whole rows or columns based on the value of one item. e.g. To highlight a whole row of data in the range A2 to E10 if the value in the first column of that row was #N/A, you could select that range and apply the conditional formatting formula:

=ISNA($A2)

The absolute part of the formula ($A) means that it always looks at the first column, but the relative part (2) means that it uses the value from the current row.

Similarly, =ISNA(A$2) could be used to format whole columns of the range A2 to E10, based on the value of the top row.

You could use the below as your conditional formatting rule:

=IFERROR(A1,"error")="error"

Change A1 to your first cell, then apply it the the whole column.