How can i apply a conditional formatting rule to all rows except first row?

Question

My problem is about programming in excel (create a formula for conditional formatting rule).

If i use the following formula in my format condition formula:

=INDIRECT("A"&ROW())>1

And apply on all rows include first row with following range in Apply to field:

$A:$F

Result: It works well.

---

But i want apply it to all rows except first row. So i changed it to:

=AND(INDIRECT("A"&ROW())>1;ROW()>1)

Result: Now it did not work on any row.

---

What is my mistake in the above formula?

Answer 1

Just add an extra formula rule to the row(s) you would like to exclude. Set the formula to TRUE and tick the Stop If True option. This rule should be on top of the rule list.

Answer 2

At first your question is confusing. You can "apply a conditional formatting rule to all rows except first row" by applying it not to $A:$F but to $A$2:$F$1048576.

But I suspect you want apply it to all rows in columns $A:$F but it shall then exclude row 1 while testing conditions.

If so your problem is the volatile behavior of INDIRECT. But INDIRECT is not needed here. Your first condition can also be written as:

=($A1>1)

This is because even conditional formatting will respect difference between fixed and variable cell references by using $ or omitting $. So this formula applied to $A:$F will check whether $A1>1 in first row, $A2>1in second row, $A3>1in third row and so on. This is because the column $A is fixed using $ and so is absolute reference to column A but the row number 1 is not fixed and so is a relative reference to the actual row..

Applied to $A:$F

this will have the same behavior as your INDIRECT formula, which is: Do Format current row if value of first cell in this row is greater than 1:

Then to exclude the first row is possible using:

=AND($A1>1,ROW()>1)

---

One of the disadvantages while using relative cell references in conditional formatting is that they must be changed if the range on which it is applied changes. For example in this case if someone inserts a row above row 1.

But even then INDIRECT is not necessary. Then we can use INDEX - MATCH:

=(INDEX($A:$A,ROW())>1)

and

=AND(INDEX($A:$A,ROW())>1,ROW()>1)