Conditional Formatting: "Format values where this formula is true" - How do I ensure that the formula fills down?

Question

The formula I have in the "Format values where this formula is true" for Conditional Formatting is:

=ISBLANK(A8)

However, I would like the formula to fill down so that it checks whether the next row down is blank before applying the format.  How do I do that?

Answer 1

It sounds like you only applied the Conditional Formatting rule to a single cell.

Select the entire area that you want to apply the Conditional Formatting to. Then recreate the rule using your =ISBLANK(A8) formula where A8 is the cell in the top-left corner of the area you selected. As noted by HansV above, the lack of dollar signs ($'s) for relative positioning is important.

When you have completed this, you can use Conditional Formatting, Manage Rules to delete the original rule.

Answer 2

The cell reference in your formula is relative, so Excel should automatically adjust it when used in other cells.

If you had used =ISBLANK($A$8), the cell reference would have been absolute, i.e. it would not be adjusted.