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:
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
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.
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.
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:
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.
=ISNA(A$2) could be used to format whole columns of the range A2 to E10, based on the value of the top row.