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.
Download Practice Workbook
You can download the practice workbook from the download button below.
3 Ways to Copy Conditional Formatting But Change Reference Cell in Excel
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 also 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.
- Excel Formula to Copy Cell Value to Another Cell
- How to Copy Only Visible Cells in Excel (4 Quick Ways)
- Copy a Worksheet in Excel (5 Smart Ways)
- How to Copy and Paste Multiple Cells in Excel (8 Quick Methods)
- Exchange (Copy, Import, Export) Data Between Excel and Access
2. Copy Conditional Formatting Using Paste Special
The next easiest way to copy conditional formatting is to use the Paste Special feature inExcel. 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.
Now you know how to copy conditional formatting but change the reference cell also. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.
- Copy Paste Vertical to Horizontal in Excel (2 Easy Ways)
- How to Copy Chart Format in Excel (3 Easy Ways)
- Copy from PDF to Excel and Keep Columns
- How to Copy Above Cell in Excel (3 Easy Ways)
- [Fixed!]: Microsoft Excel Cannot Paste the Data as Picture
- How to Copy PDF File Names into Excel (9 Efficient Methods)
- If Value Exists in Column Then Copy Another Cell in Excel (3 Ways)