Conditional formatting copying incorrectly.

[MyEXCELRL]

Hello

I am using Microsoft Excel for Mac, Version 16.72 as part of Microsoft 365 updated today. Mac operating system Monterey 12.6.5.

When copying a formatted cell to the row directly beneath, it appears to be corrupting the new and original conditional format. When I copy the format across the row, it behaves as expected, when I copy the conditional format several rows beneath it behaves as expected.

Thanks, Ryan

Correct conditional formatCopied and pasted to second cell. Still correctCopied and pasted to cell two row below. Correct conditional format.Copy and paste to cell directly below. Conditional format changed and linked to cells above incorrectlyOriginal conditional format changed by copying below.

[#2]

@MyEXCELRL 

I appreciate the fact that you say 

When copying a formatted cell to the row directly beneath, it appears to be corrupting the new and original conditional format. [emphasis added]

I use Conditional Formatting very carefully. I'm also on a Mac system (which may or may not be relevant; I suspect not)... my basic learning vis-a-vis conditional formatting in general is that it's tricky. I find when I'm expecting to create a rule, or a set of rules, that will apply to multiple rows or columns, it works best to select all of the anticipated rows or columns, rather than to set the rule(s) to one row and copy to more. It has to do with the relative and absolute references, and how they're managed during the copying.

That, and just in general, until you're really really comfortable with how Conditional Formatting works, expect it to be a matter of trial and error before you get it to work exactly as desired.

Here, for example, is a set of rules I've created that apply to one column based on values in different cells in the same sheet and even in a different sheet in the same workbook. In creating them, I selected that whole range under "Applies to" and then test each rule to make sure it's working before I go on to the next. Notice the different uses of absolute and relative ranges.

[#3]

Hi @mathetes,

Thanks for your reply. I actually suspect being on Mac could be something to do with this, especially when it comes to formatting. Whilst writing up a big document on Word for Mac, I had problems with formatting there too. In that case the formatting being correct was essential and the only fix was to do my formatting in Word on a borrowed Microsoft computer.

I appreciate you taking the time to share your thoughts. I do still wonder what is going on here though. I can copy the conditional format into cells in the same row and into rows further below and it works perfectly (this did require changing the reference cell from an absolute to relative reference). This indicates that the copy and paste of the conditional format can work. 

It seems strange that identical action is executed very differently depending on whether it is pasted into one row versus another. I'd be really curious to hear whether this problem can be replicated by others on Excel for Mac.

I have now found a work around, but it's not ideal for future as it took longer to do.