Forum Discussion
Keep conditional formatting range when inserting/deleting cells/rows/columns?
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.
Was having the same issue and so my search led to this forum, searched everywhere else and couldn't find the answer, played around with it and finally figured it out. It's actually a pretty simple solution.
Instead of Inserting a column or copying and inserting a column, all you have to do is select the column cells that you want to extend, then at the bottom of the cursor where the plus sign is, click and drag to the right as many columns as needed.
The CF range extended to the last column without creating any extra conditions or messing up the original range.
41 Replies
I believe I had the same problem, I have large sheet with conditional formatting across the whole sheet, so if a value in Column C was "XXX" then the whole row would grey out. I then decided to insert some columns from another spreadsheet into Columns EFG. The problem was the data I was inserting was unique whereas the spreadsheet contained duplicates (long story but I am reconciling different data sources to create a golden source so I need the wrong stuff too). Anyways.... When I tried to insert cells across EFG to push my inserted data down because there was a dupe on a row, all the CF went with it. So now my XXX grey CF was broken. Which surprised me as as CF rarely moves and my sheet had a CF applied $A$1:$CC$9999 so what to do? My work around was lots of copy and pastes to pick up EFG from where it was and paste it below and then go back and delete the data in the row above. It was manual but took half an hour.
- I was looking for a way to colour the weekend rows of a vertical timeline without moving when adding or deleting cells in a column. This is my solution:
Create a separate conditional format rule for each column (yes, it was time consuming)
- The range should be for that column only
- Use an offset formula to reference the days of the week cell.
In my case, days of the week were identified in column B.
so the condition formatting formula for column C would be =or(offset($C2,0,-1)="Saturday",offset($C2,0,-1)="Sunday")
the range would be =$C$2:$C$100
I've tested this and can confirm it works. Perhaps an offset formula can work for other scenarios. 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.
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.
JKPieterseThank 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
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.
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.
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!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...
- 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
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.
gahh... I've been all over the internet looking for an answer to this. Even when I try this, it takes the formatting from the cell above. So I need Cell N# to be green if it is equal to the corresponding cell in column E. So kinda like this, =$E$11=$N$11 and formatted to Green color and Green text. And then one for > and one for < in Red. But when I add a row, whether it be in the middle, or bottom, it seems to pull the formatting from the previous row. Making it look like =$E$11=$N$11:$N$12 and same for the <> ones as well. Its telling that cell to equal the line above. Which in turn will always make it Red cause it is not going to equal the line above most times. And it won't allow me to change it without messing up all the conditional formatting for the lines below it. This is so frustrating.
- 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).
Microsoft, couldn't you just add an optional fixed flag to the range option? Everyone is sick of this issue, and the only workaround I've ever found is to use event to re-establish the range, which his just daft.
Was having the same issue and so my search led to this forum, searched everywhere else and couldn't find the answer, played around with it and finally figured it out. It's actually a pretty simple solution.
Instead of Inserting a column or copying and inserting a column, all you have to do is select the column cells that you want to extend, then at the bottom of the cursor where the plus sign is, click and drag to the right as many columns as needed.
The CF range extended to the last column without creating any extra conditions or messing up the original range.
- Not so good if you need to insert data into a range rather than just extending the range, or have a I misunderstood?
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.
- Solution to this it anchor but both column and row value.
If you want whole column Instead of doing $A:$A do=$A$1:$A$100(pick range more realistic to your needs)
Adding new row will change from $A$1:$A$100 to $A$1:$A$101 instead of excluding it
If you to do multiple columns just from $A:$C just do $A1:$C100 instead.
Test it out your self see if you can find any bugs i might have missed. But solve the core problem. Interesting that you apply CF to entire sheets (entire rows/columns also works). In my experience that is a very unusual practice but one that appears to work in that it does not expand the used range or appear particularly resource intensive. I fully agree that it is a pain that this antiquated bit of functionality will not accept a named range as the range to which the formatting is applied but, instead, follows its own rules to generate collections of individual cells.
Because of the way in which CF works, it appears to be perfectly normal for the .AppliesTo range to look like something the dog has chewed. Constant repair seems to be called for!
You won't have encountered it yet, but the inability of CF to recognise dynamic array ranges is also a severe limitation. I think Microsoft is aware that conditional formatting falls well short of expectations, but the code base appears to be ancient and will require a huge amount of effort to rectify its shortcomings.
I don't apply CF to entire sheets. Only ranges within the sheet.
- That would be my preferred option, too. I found it interesting that, while large ranges of conditionally formatting affect the used range, entire CF columns or sheets do not change it.
