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.
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.
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.
- How to Make Yes Green and No Red in Excel
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting to Another Sheet
- How to Copy Conditional Formatting to Another Workbook in Excel