I'm trying to make that the cells from A3 to C3 be always formatted as you can see in this image https://i.stack.imgur.com/0vLRE.png

But, when I insert new rows above that row that's formatted, the format is then applied from A4 to C4 as you can see here: https://i.stack.imgur.com/d80ww.png

How could I format A3 to C3 cells in a way that I can insert new rows above but the format doesn't move and be kept in A3 to C3 cells ?

Thank you

  • This is a very interesting question. As far as I know it can't be done without using a macro. There might be a settings change within Excel, or it might be possible using the protection features but I think that those would have undesirable side effects.
    – Ack
    Mar 30, 2020 at 23:04

2 Answers 2


Based on Justin Doward's reply, I would add some information.

Using "And" to combine the row number with your previous formula. Please enter the formula "=AND(ROW()=3,$A3:$C3>0)".

Apply formula to columns, enter "=$A:$C" in "Applies to".

enter image description here

enter image description here


You can apply conditional formatting to the ranges or columns you are interested in, use the formula-

=if(row() = 3, 1, 0)

If you do not format the entire columns using the rule it will fail if you insert the wrong combination of rows, with the entire columns formatted this way it will maintain the range

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .