Copying Relative Conditional Formatting

Question

I have a sheet that color a number of cells in a group based on one cell. I need many copies of this group, and I often need to delete and re-add them.

How can I copy and paste conditional formatting without it linking to the original source?

When I try with fixed references, copying and pasting just follows the original location (so B3 in this example). Changing B3 would result in all the boxes on the right (copied rule) changing.

Using relative references, it seems to completely ignore the rule. The 'applies to' section looks correct, but it only applies it to the top left cell.

Is this actually possible? Am I doing something stupid?

I'm using an old version of excel if that factors into this (2007?). I can upgrade if given a compelling reason.

• I would appreciate any title change suggestions if this is inappropriate as I am unsure of how to phrase this questions.

Solution

Excel interprets the cell references in Conditional Formatting in reference to the range that it applies to. Since you have multiple rows and columns in that range, you need to fix both row and column in your formula. That is why you got that result in your second graphic. If instead you had Formula: =$B3="Blue", you would have seen the first row in blue. Conversely, if you had Formula: =B$3="Blue", the first column would be in blue.

One way to work around this is to put a conditional format rule for each row and use the Formula: =$B3="Blue" for each formula. And repeat for each color. However, if you have 6 rows you will then have 18 rules instead of 3. If that is not an issue, then when you copy it should follow appropriately.

Comments

[David]

Thank you. This works. I have to do a little manual work, but once it is done I can copy the box on the same vertical line.