Keep conditional formatting range when inserting/deleting cells/rows/columns?

[Kjell Rilbe]

Hi,

I sometimes use conditional formatting. For each entry, there's a cell range that it applies to. Often I need it to be used on the entire sheet, or at least a large range of it, i.e. all rows that have content.

But then I need to insert or remove data, and that mucks up the range. It seems to copy the entry to the range above, the inserted range, and the range below. I want it to keep the range and entries as they are when I edit the sheet's data.

How?

I usually enter the range as e.g. $1:$1048576 or $A:$XFD.

[#2]

@OldGitSean that's correct, inserting data into the middle of the range will cause the formatting to be modified.  Only works if you are extending the range to new cells at the end of the range.

[#3]

@Kjell Rilbe 

I don't call myself an "Excel Expert" by any means, but I like to struggle with things until I find some sort of simple solution.

In my case, I have 3 x CF rules applied to 2 different columns in a table, as example: "=$Q$2:$Q$185"...

As soon as I insert a row, it splits the "Applied to" range, and add a set of 3 new rules. But if I add a row to the very bottom of this table and apply my "custom sort" instruction, the range gets extended to "=$Q$2:$Q$186"...

This is what worked for me and hope someone can also make use thereof.

[#4]

I confirm Ruan88's solution :
1 add data to the bottom of the range
2 resort the range

Note that moving the row manually (using shift+drag) from the bottom to the middle of the range will break Conditionnal Formatting (unlike sorting the range).

[#5]

I figured it out. My example is column-based formatting and when inserting a new column the static cell formula and conditional formatting ($I$10) becomes $J$10 which I do not want.

I found that making the formulas and CF as I$10 then inserting a new column and dragging it over (becomes J$10) then deleting my column will re-align the J$10 to become the original I$10. This applies to the CF as well.

Took a little longer than I hoped to get this working and it was a pain. Excel should respect static cell inputs or make absolute static cell inputs (e.g. $$I$$10) so it is respected no matter what happens to the sheet elsewhere.

[#6]

CF formulas work precisely the same as cell formulas, so what you describe is to be expected. That being said, having a way to "absolutely-absolute" refer to cells would be nice indeed. You could add a suggestion here: https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472

[#7]

@jalmeter 

Hi, same problem here. Working with 3 x 30'000 datasets in one sheet we have to insert singular sets of 8 rows (example K-R) per line v downwards. Excel always presses preference >rightwards.

Whole blocks of columns destroyed

Tried to re-edit the dialog box into permanent "downwards" option, no chance.

If you'd find any solution, please get in touch.

[#8]

@Gitgo 

I suspect the problem is that conditional formatting is an antiquated piece of junk that will take a fortune to sort out.  You are more likely to get added 'bells and whistles' than a substantive rewrite.  

The main challenge now is to create something that works well with dynamic arrays (I use 365 and my solutions comprise nothing but names and dynamic arrays).  If I create a dynamic array of formats (which may cover header rows, numeric results, white space between output blocks), I want it to apply to the range used by the dynamic array, whatever that happens to be at the time.

[#9]

For a year now I've been redoing the conditional formatting every couple of days. Yes, It's crazy this hasn't been addressed. The same thing happens with the Data Validation, inserting lines stops that from working and I have to manually set it again.

It feels like we're using Excel in the year 2000, a spreadsheet for the new millennium! The new AI stuff makes bad design like this seem so out of place in 2023.

[#10]

I have a situation where I have one column formatted and I am just cutting and pasting cells from the other columns to a different line. No column or row insertion, simple cut/paste. The format only applies to column F. So I am highlighting A-D of one row, say 15, and I cut, then past the values onto row 10.

I can tell right away that it's screwing things up, as the paste triggers the CF to turn column F green if it matches column A.

It somehow still screws up my conditional formatting that is only on column F.

Explain that one!

[#11]

@kennnels - instead of "cutting" or even dragging the data to a new position, I would rather copy and paste as value(V).

If you think about it, you are physically moving the reference of the formula in F15, to a new position. Therefor the original formula of F15 will autocorrect it self and will now reference the data in row 10.

As an example, I created a table A2:B9. I use a formula (=A2*$A$1) throughout a entire column B, to reference a value, originally positioned in A1. If I "CUT" A1 and paste the data in A20, the formula of the entire column will autocorrect as (=A2*$A$20) and all values will still be calculated correctly. Even if you move the entire table to column Z, the formula autocorrect to (=Z2*$Z$20)

In many cases, this is what you would want, but in your situation, you should rather just copy and paste as value(V).

PS: I am no expert and stand to be corrected...

[#12]

@Kjell Rilbe 

I had the same issue.  The solutions suggested in this blog didn't work for me since the formatted range included only several columns in the middle of the worksheet, but excluded other populated columns to the left/right.  My solution was to write a quick macro that selects the formatting range, deletes all current conditional formats, then rewrites the format I want.  It's easier for me to run the macro than to manually reset the formatting range after every edit.  Clearly not a solution for everybody.

[#13]

Call me crazy but IMO if I specify $E:$E in a conditional format, it should remain $E:$E regardless of adding or removing rows.

[#14]

@stacyalabardo Adding or removing rows will not affect a reference like $E:$E as that points to all rows. Try a regular SUM formula like that in a cell and start adding rows. Watch the formula. CF formulas work exactly the same.

[#15]

@Jan Karel PieterseThank you for responding! Unfortunately, that hasn't been the case with my version of Excel for Mac, Version 16.76 (23081101) on my 13-inch, 2020, 2.3 GHz Quad-Core Intel Core i7 MacBook Pro with 16 GB of RAM. This is happening in a shared file across a network. Does that make a difference given that everyone probably doesn't have the exact same computer as me? Also, some of those folks use the browser version of Excel. Could that be the reason? It happens mostly when a row is deleted. Your expertise would be helpful. TIA

[#16]

I forgot to mention, all my conditional formatting pertains to columns.

[#17]

What -precisely- happens? Is the CF rule not extended to include the new row by any chance? You can prevent that from happening by first copying a row and then right-clicking and choosing insert copied row.

[#18]

Yes, the CF is maintained when a row is copied and pasted as you describe.

The issue comes when a row is deleted. The CF range then changes from $E:$E to something like $E$1:$E$20,$E$21:$E$10k (10k=whatever the max row number is in Excel) and it becomes unwieldy to maintain.

I will also find CF rules duplicated when I check the CF for the Worksheet.

Could this be caused by the browser version of Excel of copying/pasting a row?

FYI, I have 42 conditional format rules.

[#19]

@stacyalabardo Microsoft issued a fix quite recently for this for Windows Excel. I am not sure if this was also implemented for Mac (or on-line).

Does it work any better if you apply the CF rule to a range formatted as table?

[#20]

I can look into that and I will hope that MS will extend that fix for us Mac users soon. Thank you for all your help!