How to Copy Conditional Formatting But Change Reference Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

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!

dataset to copy conditional formatting but change reference cell


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)

copy range where conditional formatting is applied

  • 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.

use Format Painter to copy 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.

conditional formatting copined to the next column

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.

Read More: How to Copy Conditional Formatting to Another Cell in Excel


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.

copy conditional formatting using Paste Special

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

Paste Special dialog box

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

Paste Special copies conditional formatting

Read More: How to Copy Conditional Formatting with Relative Cell References in Excel


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.

Manage conditional formatting rule

  • 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.

create duplicate conditional formatting rule

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

edit dulicate conditional formatting 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.

remove $ signs from reference cells

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

update "Applies to" range

  • Then click on Apply and then OK.

copy conditional formatting to new range

  • 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.

conditional formatting copied to new range

Read More: How to Copy Conditional Formatting Color to Another Cell in Excel


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.


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.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo