Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel - Conditional Formatting - insert row

Using Offset or Indirect in 'Applies To' does not seem to work. Is there any other way to stop conditional formatting from breaking after inserting row/s

I have a conditional format for a range for e.g. $O$19:$O$105. The condition is 'if cell value is > 10', it is formatted with red color.

The problem is - when I insert a row in excel, this formatting range splits and I get 2 formatting rules. For e.g. the 2 rules with range as $O$19,$O$21:$O$105 & $O$20 respectively, if I insert a new row at 20th row.

Typically for condition like the one above, it may not matter, if the rules are split into multiple ranges. But for conditions like 'highlight top 10', it causes undesired results.

I tried the following without much luck:

  1. Tried using indirect - but excel seems to resolve the formula and saves the formatting rule and hence does not work with inserts as expected
  2. Tried using offset - here again excel resolves the range same as above.

Anyone knows how to write a conditional format that does not break with row inserts?

[EDIT] I realized that insert row is not causing the splitting of the conditional formatting rules. I also copy a row and paste in the inserted row which is doing this. If I opt for special paste and choose formulas only, its working fine.

like image 319
ssp Avatar asked Sep 18 '12 16:09

ssp


People also ask

How do I conditionally insert rows in Excel?

Is is possible to have excel automatically insert a row when certain condition are met. - Assuming your data is in column A (starting in row i), insert a column before column A. - In the inserted column, select from A2 down to the last data row. - Type in =IF(A2=A1,1,"") and press Ctrl+Enter.


1 Answers

I know this is an old thread but here's another solution that's super simple and works great.

Simply insert a new row or column as desired. Then select and copy a row/column that has the correct conditional formatting. Past Special into the new row/column that you just created and select the option for "All merging conditional formats". Your conditional formatting rules should now be automatically updated.

Happy Excel-ing =)

like image 110
user3641891 Avatar answered Sep 18 '22 11:09

user3641891