I have the following annoying problem with Excel 2010. I use Conditional formatting to apply bold formatting to each line of the spreadsheet that meets a condition expressed by a formula. In the "applies to", I write =$A$2:$Q$149, which is the current extent of my spreadsheet. But I want this range to grow with my spreadsheet, i.e., I want the formatting to permanently apply to the whole spreadsheet. Regularly, my "applies to" field is transformed into a complex range, that selects most lines of spreadsheet, but not all, like =$A$2:$Q$138;$A$140:$Q$147. Have you ever encountered such a problem? Is there a solution?
The solution is to use a dynamic defined range. I usually use the offset function. You can find some help here. When you have defined the dynamic range, you can use as a range in your conditional formatting.
When you click "Apply" and then "Ok", the formatting should be applied to that range.
If you return to the conditional formatting, you'll see that the dynamic range is actually translated to the row-column notation. However, I've noticed that if you add a row to your dynamic range (by filling in a blank cell), this is automatically propagated to the conditional formatting.
One workaround is to make your data a dynamic named range. This will allow you to apply the conditional formatting to the named range by name, which will remain constant, while the range the name applies to may change as you add or remove data.
How to set up a dynamic named range:
Go to the Formula ribbon and click
Name Manager. In the Name Manager, click
New... to create your named range. Give it a descriptive name (no spaces) like
MyData. In the
Refers to field you can use a formula to define the named range. Something like
will refer to all data in
A:Q assuming there are no blanks in your data in column A. Now you can use the name
MyData to refer to all your data, even if you add or delete rows.
All that's left to do is to redefine your conditional formatting rule with
MyData in the
Applies to field.
Microsoft needs to add this feature which means referencing Table name in conditional formatting.
It's something like below.
Applies to: =Table1[Column3]
So many people requesting this feature since 2015 but it's not being implemented yet.
Please vote for this feature.
I've run into this before. This is very similar to the question Excel conditional formatting fragmentation.
I am pretty sure you can use the same solution, only enter
=$A:$Q as the range in the
Applies to field.