8

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?

user
  • 29,449
  • 11
  • 99
  • 144
Ala_Poubelle
  • 81
  • 1
  • 1
  • 3
  • possible duplicate of [Excel conditional formatting fragmentation](http://superuser.com/questions/598368/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 03: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 04 '16 at 00: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 08 '16 at 01:39

5 Answers5

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.

stedes
  • 121
  • 2
  • 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.

Excellll
  • 12,627
  • 11
  • 51
  • 78
  • 7
    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. – Ala_Poubelle 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 09: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

wonsuc
  • 101
  • 1
0

There is a nice workaround for it, which I found in the MrExcel.com forum: https://www.mrexcel.com/board/threads/dynamic-range-for-conditional-formating-on-shared-sheet.939723/#post-4516043

  1. Clear your existing rule.
  2. Select the entire sheet by clicking the triangle between 1 and A
  3. Click Conditional Formatting > New > Rule > Use a formula > and enter this formula:
    =AND($G1=Guide!$A$7,ROW()>1,ROW()<=6000,COLUMN()<=14)
  4. Select your format.

That rule will apply to A2:N6000 even if someone adds/deletes rows/columns.

It basically says: Choose a larger range (e.g. the whole spreadsheet or a set of columns or rows) and limit the range to which the formula is applied inside the formula.

In my case, the spreadsheet C1 contains the number of the first row and C2 contains the number of the last row of the range, to which I want to apply the conditional formatting. The column of the conditional formatting is 'F'. The goal is to mark empty cells in column 'F' red, if the corresponding cell in column 'B' has a value.

The formula is
=AND(ISBLANK($F1); NOT(ISBLANK($B1)); ROW()>=$C$1; ROW()<=$C$2)
Original formula in German:
=UND(ISTLEER($F1); NICHT(ISTLEER($B1)); ZEILE()>=$C$1; ZEILE()<=$C$2)

The "Applies To" range is =$F:$F

Explanation:
"AND(ISBLANK($F1); NOT(ISBLANK($B1)))" is the actual condition.
"ROW()>=$C$1; ROW()<=$C$2" is the additional range limitation to the "Applies To".

Adjust this range limitation to your needs.

Tobias Knauss
  • 361
  • 1
  • 9
-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

Community
  • 1
CharlieRB
  • 22,566
  • 5
  • 56
  • 105
  • 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. – Ala_Poubelle 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. – Ala_Poubelle Jul 23 '14 at 08:28