Copy conditional formatting to columns when using a formula

Question

Hi
When I am using a formula in conditional formatting.

=LARGE(($E$5:$E$21),3) - I have also used relative =LARGE((E5:E21),3) it does not copy the formula to the next column like =LARGE((F5:F21),3)

also when I copy the rule and then change formula for the cell it is still connected to the cells I copied so it changes them too?

ie I duplicated to 3 columns then changed all formulas and found it had changed formula to the last change to all cells I had duplicated.

It seems connected - surely if you duplicate for each cell it will know it would be for that column when you change formula? Is this a bug?

The only cells that are working are the ones I added manually to each.

Is that the only way?

Any way to copy cells and formula ?

Answer 1

An example might make the solution easier:

A CF that colors the largest number in each column, formula used in E, copied to F:G

=E1=LARGE(E$1:E$21,1)

Sample file:
https://www.dropbox.com/scl/fi/lt0mrqe3n1namzzvqoxne/9b011f5e-d590-4b46-a184-7f528ce56059.xlsx?rlkey=1upw9962x589mfq18n31sc97f&dl=1

Andreas.

Answer 2

I was asking how you can copy a cell and the formula changes relative the cell.

Excel does that already and automatically.

The point is that you can not see that inside the CF manager as you expect. In there, all same formulas are combined automatically, only the formula of the top left cell is preserved and the applied range is enlarged.

Simple example, if you have a formula like =A1="x" applied to A:A and you apply this to B:B I understand that you think you get a new formula =B1="x" which is applied to B:B

The relative reference inside the formula is the same, therefore the result is the same if we apply =A1="x" to A:B and that's what you see inside the CF manager.

Andreas.

Answer 3

ps I will look at what you sent ..

Answer 4

Thanks Andrea

The formula is not copying and changing relative to the cells when you copy to adjust cell it would change from E to F for example, ( I di remove the absolute $)

and if I change any cell it changes in all the cells with the formula that I cell I copied BUT not the cells I manually added.

So they are some how connected

I was asking how you can copy a cell and the formula changes relative the cell.

thanks

Answer 5

There is no issue, the behaviour is normal.

If you use a formula with relative references inside a CF and apply to more then one cell, Excel changes the formula internally same way as if you drag the formula over the cells. See also:

https://answers.microsoft.com/en-us/msoffice/forum/all/conditional-format-how-to-develop-a-formula-for/ee201274-a792-4b29-9ff0-26c0f80f4424

The confusing part is that you can not see that inside the CF manager if you select a different cell. The formula is the same for the applied range, means the formula is shown for the top left cell of the applied range only.

Andreas.