I have a table that looks like in Google Spreadsheets:

enter image description here

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.

enter image description here

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.

enter image description here

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:

enter image description here

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

enter image description here

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


2 Answers 2


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

  • Thanks for your answer! I'm sorry, but I don't understand how could I use to solve my issue. I can already count how many rows my named ranges are (and I could do that without using INDIRECT, just COUNT), but I have no idea on how I'd plug this into conditional formatting.
    – Saturnix
    Commented Mar 2, 2018 at 1:11
  • I'm sorry if this wasn't clear, but formulas are not the problem here: those can already refer to named ranges. I've updated my question with another screenshot.
    – Saturnix
    Commented Mar 2, 2018 at 1:13
  • @saturnix I removed the "noisy" part of my answer. Commented Mar 2, 2018 at 1:16

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.