Copy Conditional Formatting from Range to Another in Excel

In Excel, the easiest way is to use the format painter when copying and pasting the same conditional formatting to another cell or a range of cells.

But apart from this, there are two more ways that you can use, and in this tutorial, we will look at all these methods to learn about them.

In the above example, we have data bars applied to the quantity column, and now we need to copy from there and paste it into the amount column.

Copy Conditional Formatting with the Format Painter

1. First, select the range from where you want to copy the conditional formatting.
   
2. Afterward, go to the home tab, and click the format painter icon.
   
3. Your cursor will change into a paintbrush once you click the format painter button.
   
4. Now, select the range you want to paste the conditional formatting.
   

Yes, that’s it.

Note: If you want to paste conditional formatting to multiple sections, you can click on the format painter twice and then select multiple ranges to apply the conditional formatting you have copied.

Copy Formats (Paste Special) to Copy Conditional Formatting

With the Paste Special option, you can copy and paste only formats from one place to another. Once you select the range from where to copy the conditional formatting, use the copy option or the keyboard shortcut Ctrl + C to copy.

Next, select the range you want to paste the conditional formatting. After that, open the special paste option (Right Click > Paste Special), and click on the formats.

In the end, click OK. And the moment you click OK, it applies the conditional formatting (which you have copied) to the selected range.

Create a Duplicate Conditional Formatting Rule.

If you go to the Conditional Formatting > Manage Rules, you can see the list of all the conditional formatting you have in the workbook.

Once you open it, select “This Workbook” from the drop-down list “Show formatting rules for”.

It will show you all the rules in the workbook for conditional formatting. And then, you need to select the formatting you can copy and click on the Duplicate.

Once you do that, change the range address from “Applies to”.

I have changed the range address from B2:B13 to C2:C13 to apply the same conditional formatting to C2:C13. And in the end, click OK.

A Problem You Might Face

Copying Conditional Formatting is a smooth task. But there can be a few issues when using custom formulas to define a rule for conditional formatting.

Let me give you an example. In the below example, we have conditional formatting using a custom function. In the formatting, if cell A1 has a value, then cell B1 will have a green cell color.

Here’s the rule that we have applied (Home Tab > Conditional Formatting >Manage Rules > Edit Rule)

Now, if you copy and paste this conditional formatting to cell B3. As you can see, in the below example, cell B3 is green even the cell A3 doesn’t have a value.

When you copy and paste this conditional formatting, Excel won’t change the reference cell because the reference is absolute.

You can see above the reference cell in both rules is A1. So, yes, that’s the problem. To correct this problem, the best way is to use the relative reference that allows you to copy and paste the conditional formatting to a new range or cell and change the reference in the formula.

What IF I Want to Copy a Conditional Formatting Rule to Another Worksheet

You can use the same methods and steps above to copy it from one cell to another or a range of cells. Once you copy it, you can paste it to another worksheet.