7

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?

5
  • possible duplicate of Excel conditional formatting fragmentation
    – Excellll
    Jul 18 '14 at 15:37
  • @Excellll I thought about voting as duplicate before I posted the answer, but didn't feel it's an exact duplicate. It's very similar, but one relates to columns while the other is about the whole table.
    – CharlieRB
    Jul 18 '14 at 17:14
  • Applying a format or conditional formatting rule to the entire spreadsheet may bog your spreadsheet down. Use dynamic named ranges instead (see Excell's answer).
    – Ellesa
    Jul 21 '14 at 3:44
  • Copy a cell with formatting you want, paste-special, formatting only, to all desired cells. There is a detection script, but that would be a VB script solution and "script writing service" is frowned upon.
    – ejbytes
    Jun 4 '16 at 0:31
  • $A$2:$Q$138;$A$140:$Q$147. Why stop there? Your conditional formatting will skip over blank spaces and cells that don't matter. Check your conditionals to make sure you have as many conditional checks that makes it possible. Why not just A2:Q5000?
    – ejbytes
    Jun 8 '16 at 1:39
2

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.

3
  • The last comment about how excel does actually change the range if a row is added is very important! I had no idea this worked. I've also noticed that if you add a row in the middle of the data, even though the rule splits as usual, it the the new row does get covered if the rule was applied in this way
    – Some_Guy
    Aug 15 '17 at 15:24
  • 1
    This applies to normal named ranged (non dynamic) and table based ranges as well. If you insert a table, and apply the conditional formatting to "table1", even if the rule doesn't appear to apply to the table, the definition will in fact expand if the table size changes
    – Some_Guy
    Aug 15 '17 at 15:49
  • 1
    I have found this not to be the case in Excel 2010 (the Conditional Formatting Rule's Applies To field did not update when I changed the Area of the Named Range it was based on), but I updated the Named Range in the Name Manager and not by modifying the worksheet (by inserting rows/columns). Can you confirm what version of Excel you used and how you modified the named range?
    – 4AM
    Jun 26 '19 at 18:18
1

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

=INDIRECT("Sheet1!$A$1:$Q$"&COUNTA(Sheet1!$A:$A))

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.

2
  • 6
    My database is already named Table2 in the name manager, but when I copy =Table2 in the "Applies to" field, it gets transformed into the actual range, i.e. =$A$2:$Q$149. Jul 18 '14 at 15:51
  • Good point @Ala_Poubelle, I have the same problem which makes this solution not dynamic at all. It's the same that writing the formula in the "Applies to" field. I believe we have made something wrong, though, because they have suggested as a dynamic solution. Any idea on how to fix what Ala_Poubelle spotted?
    – soneangel
    Apr 10 '19 at 9:53
0

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.

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/10561194-conditional-formatting-apply-to-named-ranges

-2

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.

enter image description here

3
  • Thanks for pointing out the duplicate. You should vote to close.
    – Excellll
    Jul 18 '14 at 15:41
  • Thanks, I'll try that, and close if I do not encounter the problem again after a while. Jul 18 '14 at 15:52
  • 3
    This does not work. After applying the suggested modification, I found the formula had been modified to =$A$1:$Q$153;$A$157:$Q$1048576;$A$154:$G$156;$I$154:$K$156;$M$154:$Q$156 in my spreadsheet. Jul 23 '14 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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