How to Copy Conditional Formatting Color to Another Cell in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


Download Practice Workbook


3 Simple Methods to Copy Conditional Formatting Color to Another Cell in Excel

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.

excel copy conditional formatting color 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.

Steps:

Note: Here, we highlighted the cells that have values greater than 80.

Using Paste Special Option to copy conditional formatting color to another cell in Excel

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

Final output of method 1 to copy conditional formatting color to another cell in Excel

Read More: How to Use Paste Options 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.

Steps:

Utilizing Format Painter to copy conditional formatting color to another cell in Excel

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

Final output of method 2 to copy conditional formatting color to another cell in Excel

Read More: How to Copy Conditional Formatting But Change Reference Cell in Excel


Similar Readings


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:

Using Conditional Formatting Rules Manager to copy conditional formatting color to another cell in Excel

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

Final output of method 3 to copy conditional formatting color to another cell in Excel

Read More: How to Copy and Paste Conditional Formatting in Excel


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.

How to Copy Conditional Formatting to Another Sheet in Excel

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

Final output of method 4 to copy conditional formatting color to another cell in Excel

Read More: How to Copy Multiple Cells to Another Sheet in Excel (9 Methods)


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:

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.

How to Copy Conditional Formatting to Another Workbook in Excel

Read More: How to Copy and Paste Exact Formatting in Excel (Quick 6 Methods)


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

Practice section to copy conditional formatting color to another cell in Excel


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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy.


Related Articles

Zahid Hasan

Zahid Hasan

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo