Use named ranges to dynamically add rows in google spreadsheets, while maintaining conditional formatting in newly added rows

Question

I have a table that looks like in Google Spreadsheets:

Some numbers in this table are calculated upon a range of cells (for instance, the total in the last row).

If I add a new row to this table, I naturally would like the total to consider the new row, without having to update every single formula. To achieve this, I use named ranges.

If I add a new row, I can simply open the named ranges window, change the size of range (say, from F2:F8 to F2:F9) without having to touch the formulas.

I would also like conditional formatting to behave like this but, unfortunately, it doesn't accept named ranges as parameters.

With this, the more computed columns I have, the more places I have to edit something if I add a new row:

This would be perfect but, as you can see, it is not accepted as valid:

How can I do this? How can I add new rows without having to update all my conditional formatting and formulas?

Answer 1

At this time it's not possible to use named ranges to the "Apply to range" setting of Conditional Formatting.

Answer 2

Use 1 row extra for your color scale:

Use F2:F9 ,When you want the color scale only over F2:F8. Since F9 is only "totals",It won't matter. Then when you add a row,Click 8:8 and add a row below/above. This also works for SUM. There's no need for named ranges.