In Excel, we often need to copy the conditional formatting color to another cell. However, we can copy the conditional formatting color for each cell individually. But there are more innovative and more efficient ways to do this. Especially, when the dataset contains thousands of rows and columns, it becomes quite impossible to copy the color individually for each cell. In this article, we will discuss 3 convenient ways to copy the conditional formatting color to another cell in Excel. So, let’s start this article and explore these methods.
Copy Conditional Formatting Color to Another Cell in Excel: 3 Simple Methods
In this section of the article, we will learn 3 simple methods to copy conditional formatting color to another cell in Excel. Let’s say we have the Marks of Grade 6 Students of a school as our dataset. Our goal is to copy the conditional formatting color of a cell to another cell.
Not to mention that we have used the Microsoft Excel 365 version for this article, you can use any other version at your convenience.
1. Using Paste Special Option
Using the Paste Special feature is one of the easiest ways to copy conditional formatting color to another cell in Excel. Let’s follow the steps outlined below to do this.
- Firstly, color cell C5 using the Conditional Formatting option.
Note: Here, we highlighted the cells that have values greater than 80.
- After that, select cell C5 and use the keyboard shortcut CTRL + C to copy the cell.
-
- Then, choose the range of the cells you want to copy the Conditional Formatting color. In this case, we have selected the range C5:F12.
- Following that, go to the Home tab from Ribbon.
- Now, select the Paste option from the Clipboard group.
- Next, choose the Paste Special option from the drop-down.
- Afterward, select the Formats option in the Paste Special dialogue box as shown in the following image.
- Then, click OK.
Consequently, the conditional formatting color of cell C5 will be copied to the range of cells C5:F12.
Read More: How to Make Yes Green and No Red in Excel
2. Utilizing Format Painter
Utilizing the Format Painter is another smart way to copy conditional formatting color to another cell in Excel. It is the fastest way to copy any type of formatting to both single and multiple cells in Excel. Let’s use the procedure mentioned in the following section to do this.
- Firstly, follow the previously mentioned steps to color cell C5 by using the Conditional Formatting option.
- Following that, select cell C5 and go to the Home tab from Ribbon.
- Then, choose the Format Painter option from the Clipboard group.
- After that, select the range of the cells you want to apply the copied conditional formatting color of cell C5. Here, we selected the range of cells C5:F12.
That’s it! The conditional formatting color of cell C5 will be copied to your selected range as demonstrated in the following picture. It’s that simple!
Read More: How to Copy Conditional Formatting But Change Reference Cell in Excel
3. Using Conditional Formatting Rules Manager
We can also use the Conditional Formatting Rules Manager dialogue box to copy the conditional formatting color to another cell in Excel. In the following section, we learn the steps to do this.
Steps:
- Firstly, follow the steps mentioned in the 2nd method to copy the conditional formatting color of cell C5 to the range of cells C5:C12.
- After that, select any cell of the History column. In this case, we chose cell C11.
- Then, go to the Home tab from Ribbon.
- Now, select the Conditional Formatting option from the Styles group.
- Next, choose the Manage Rules option from the drop-down.
As a result, the Conditional Formatting Rules Manager dialogue box will open on your worksheet.
- Following that, select the Rule in the Conditional Formatting Rules Manager dialogue box.
- Then, click on the Duplicate Rule option.
- Subsequently, click on the marked area on the duplicated rule and select the range of cells D5:F12.
- Then, click on Apply.
- Finally, click OK.
Consequently, the conditional formatting color of the History column will be copied to the range of cells D5:F12 as shown in the image below.
Read More: How to Apply Borders in Excel with Conditional Formatting
How to Copy Conditional Formatting to Another Sheet in Excel
In the previous sections of this article, we learned how we can copy the conditional formatting color to another cell in the same worksheet. Now, we will learn how we can do the same for different worksheets. Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, select cell C5 from the worksheet named Duplicate Rule and go to the Home tab from Ribbon.
- After that, click on the Format Painter option from the Clipboard group.
- Then, go to the worksheet where you want to copy the conditional formatting color of cell C5 of the worksheet named Duplicate Rule.
- Now, select the range of cells C5:F12 in the worksheet where you want to copy the conditional formatting color.
Subsequently, you will have the conditional formatting color copied to another worksheet as demonstrated in the following picture.
How to Copy Conditional Formatting to Another Workbook in Excel
While working in Excel, sometimes we need to copy conditional formatting to another workbook. Now, let’s use the procedure outlined in the following section to do this.
Steps:
- Use the same steps mentioned in the previous method to copy conditional formatting in another workbook.
Note: The only difference from the previous method is that we need to copy the conditional formatting to a new workbook instead of a new worksheet. The rest of the procedure is exactly the same.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
Download Practice Workbook
Conclusion
So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to copy Conditional Formatting color to another cell in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.
Related Articles
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting to Another Cell in Excel
- How to Copy Conditional Formatting to Another Sheet
- How to Copy Conditional Formatting to Another Workbook in Excel
- How to Copy Conditional Formatting with Relative Cell References in Excel
