How to Copy Conditional Formatting But Change Reference Cell in Excel

Conditional Formatting is a very popular feature in Excel that lets you format your dataset when applied criteria are met. But it may become a tiresome job if you need to apply the same conditional formatting again and again to different ranges. That’s when you need to copy the formatting from one range to another. This often leads to unexpected errors as the cell references may not change when copying the formatting. This article highlights 3 methods to copy conditional formatting and also change the reference cell.

Copy Conditional Formatting But Change Reference Cell in Excel: 3 Ways

We will use the following dataset to highlight the methods to copy conditional formatting with changing cell references. Here, conditional formatting is applied to the Jan column for sales greater than $20,000. The conditional formatting rule is =C5:C10>20000. Our goal is to copy the conditional formatting to the Feb column so that the formula also changes to =D5:D10>20000. So let’s start!

1. Copy Conditional Formatting Using Format Painter

The easiest way to copy conditional formatting and change the cell reference is to use the Format Painter tool in Excel. Follow the steps below to be able to do that.

First, copy any cell in the range where the conditional formatting is applied. You can also copy the entire range (C5:C10)

Then go to the Home tab, click on Format Painter, and drag the cursor to the entire range where you want to copy the conditional formatting.

After that, the conditional formatting will be copied to the Feb column and the formatting rule will change to =D5:D10>20000 as expected. As a result, the Feb column will look as follows.

Note: You must change any locked references (remove any $ signs) in the formatting rules to relative references before copying the conditional formatting. For example, you need to change =$C$5:$C$10>20000 to =C5:C10>20000. Otherwise, the cell reference will not change and you will get erroneous results.

2. Copy Conditional Formatting Using Paste Special

The next easiest way to copy conditional formatting is to use Paste Special command in Excel. Follow the steps below to be able to do that.

First, make sure the conditional formatting rules contain relative cell references (no $ sign) only. Then copy any cell in the range where conditional formatting is applied (C5:C10). Next, select the entire range where you want to copy the conditional formatting. After that, go to Home >> Paste >> Formatting (R) as shown below.

If you select Paste Special instead, then the Paste Special dialog box will open. Now mark the radio button for Formats and click OK.

After that, you will get the same results obtained earlier.

3. Utilizing Conditional Formatting Rules Manager

The least easy but most effective way to copy conditional formatting is to utilize the conditional formatting Rules Manager. We said earlier that it is a prerequisite to convert absolute references in the conditional formatting rule to relative references before copying. We will also highlight the way to do that in this method.

First, select any cell or the entire range where the conditional formatting is applied. Then go to Home >> Conditional Formatting >> Manage Rules. This will open the Conditional Formatting Rules Manager.

After that, you will see the formatting rule listed there. If you didn’t select any cell earlier where the conditional formatting is applied, then change the Current Selection to This Worksheet using the dropdown. Now select the formatting rule that you want to copy and then click on Duplicate Rule.

Then, a copy of the formatting rule will be created as follows. Select that rule and click on Edit Rule.

Next, remove the $ signs from the formatting rule to change them to relative references. Click OK after that. This will take you back to the Rules Manager.

Now click on the upward arrow beside the “Applies to” range. Then select the range where you want to copy the formatting.

Then click on Apply and then OK.

After that, you will get the same results as obtained earlier. If you go back to the Rules Manager, you will see that the cell references in the formatting rule have also been changed automatically.

Things to Remember

You must change the absolute references in the formatting rules to relative references before copying the formatting. Otherwise, the formatting will get copied but the reference cell will remain the same.
Sometimes you might not see the Duplicate Rule option in the Conditional Formatting Rules Manager. It is because of your existing Excel version. You may need to update your version.

Download Practice Workbook

You can download the practice workbook from the download button below.

Copy Conditional Formatting.xlsx

Conclusion

Now you know how to copy conditional formatting but change the reference cell also. Here I have tried to cover all the ways. Do you have any further queries or suggestions? Please let us know in the comment section below.

<< Go Back to Copy Conditional Formatting | Conditional Formatting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!