Conditional Formatting applying to old columns when new columns are copied and inserted.


I have columns that have conditional formatting applied using a CONCATENATE formula. 

When I copy and paste the new columns, the formula still uses the old columns and doesn't pick up the new ones. Please can someone help? I have attached a sample file. 


For example, columns AK & AL are picking up columns AN & AO when I copy the three columns.




20 Replies


See if this works better for you. I replaced all rules with a set of 6 rules for the entire range.

Hi Hans,
Please can you explain the logic around this?
Will it only work if the column A is locked?

@Hans Vogelaar 


This is the layout of my spreadsheet and I have attempted to use your 6 rule, however this is not working. Please can you apply to the layout attached and I will mark as best response. Thank you Hans


The formula refers to A3, not to $A3, so Excel will dynamically adjust it in other columns.

The formula first uses MOD(COLUMN(...),3) to check whether the cell is in a column that is to be formatted conditionally.

If so, it uses OFFSET in combination with MOD(COLUMN(...),3) to determine the two cells to be concatenated. For a cell in column A (or D or G etc.), it is the cell itself and the cell to the right.

For a cell in column B (or E or H etc.) is the cell itself and the cell to the left.

This makes the formula much more dynamic.


Originally, the range to be formatted began in column A = column 1. 1 = 1 Mod 3.

Now it begins in column I = column 9. 9 = 0 Mod 3.

So everything shifts.

@Hans Vogelaar 


Thank you - this makes sense. 

I have applied the formulas to the last column, however there are only a few cells which it does not apply to. Please find attached.



Which cells, for example?


Keep in mind that you have applied direct formatting to some of the cells. If none of the 6 rules applies, you'll see the direct formatting.

I have applied your formula to columns AY and AZ however some cells have not been formatted e.g. AY40 and AZ40.


Please go back to the workbook that I attached. You have somehow managed to completely mess up the rules.

I don't understand how?
I've changed the formatting to follow these 6 rules:

and ensured the 'Applies To' field reflects the data range from column I onwards.


I3 must be the active cell in the selection when you create the rules. In your most recent workbook, this is what I see if I select Manage Rules and edit one of the rules:




As you see, it does not refer to I3 but to XDW3.

Ah I understand, apologies. I've managed to get it working, however when I change a rating to F1, T1 from the workbook you attached, this does not change to the colour to green.

@Hans Vogelaar 

This is what I see within the attachment you sent back to me very strange




Could you attach the problem workbook?


(I'll be away for a couple of hours)

@Hans Vogelaar No problem, please find attached, thanks. 


I see the problem, but the only conclusion I can draw is that Excel can't handle formatting so many cells. The problem only occurs in some rows of some columns. I have checked the formulas; they return the correct values for the problem cells and yet Excel applies the rules incorrectly...

Hi Hans, so is there no way around this issue?