How do I copy conditional formatting to another cell but change the reference?

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 co

How do I copy conditional formatting to another cell but change the reference?

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 fixed references

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.

Using relative references

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.

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.

Video liên quan