How to Copy Conditional Formatting to Another Cell in Excel (2 Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you use specific conditional formatting to highlight cells based on some specific conditions, you might want to copy the conditional formatting to another cell or a range of cells to apply the same formatting on them. In this tutorial, we will learn how to copy conditional formatting to another cell in Excel.


Copy Conditional Formatting to Another Cell in Excel: 2 Easy Ways

Let’s consider a situation where we have an Excel worksheet that contains information about the marks students of a school got in Engish and Math.  We have already applied conditional formatting to the English column to highlight any mark in English that is above 80. We will now copy the same conditional formatting to the cells in the Math column. The image below shows the worksheet that has conditional formatting applied on both the English and Math column.

Copy Conditional Formatting to Another Cell in Excel


Method 1: Use the Format Painter Tool to Copy Conditional Formatting to Another Cell

The easiest way is to use Format Painter to copy the conditional formatting to the cells in the Math column. Let’s see how we can do that.

Step 1:

  • First, we will select a cell on the English column that has conditional formatting applied to it. For example, we have selected cell C7.
  • Then, we will click on the Format Painter under the Home.

Use the Format Painter Tool to Copy Conditional Formatting to Another Cell

  • Now, we will see the Fill Handle along with a Paint Brush.

Step 2:

  • Next, we will select the first cell in the Math column (D5) and drag the fill handle downward to copy the conditional formatting of the English column to the cells of the Math.

Use the Format Painter Tool to Copy Conditional Formatting to Another Cell

  • We will release the fill handle when it reaches the last cell of the Math column (D14).
  • Finally, we will see that the conditional formatting of the English column has been copied to the Math column. All the marks above 80 in the Math column are highlighted with a light green color.

Use the Format Painter Tool to Copy Conditional Formatting to Another Cell

Read More: VBA Conditional Formatting Based on Another Cell Value in Excel


Method 2:  Copy Conditional Formatting to Another Cell Using the Paste Special Feature

Alternatively, we can also use Paste Special feature of Excel to copy the conditional formatting of the English column to the cells of the Math column. We have to do the following.

Step 1:

  • First, we will select a cell of the English column and right-click on it. For example, we have selected cell C9. After we right-click on the cell, we will see a menu appears.
  • Now, we will click on Copy from the menu to copy the cell.
  • Alternatively, we can also press CTRL+C to copy the cell.

Paste Special Feature

Step 2:

  • Now, we will select all the cells of the Math column and right-click on them. Another menu will appear.
  • We will select Paste Special from that menu.

Paste Special Feature

Step 3:

  • A window titled Paste Special will appear. Now, we will select the Formats option from that window.
  • Then, we will click OK.

Copy Conditional Formatting to Another Cell Using the Paste Special Feature

  • Finally, we will see that the conditional formatting of the English column has been copied to the Math All the marks above 80 in the Math column are highlighted with a light green color.

Paste Special Feature

Read More: How to Remove Conditional Formatting but Keep the Format in Excel


Quick Notes

  • In most cases, you will not face any issue while copying conditional formatting to another cell in Excel. But you may run into problems if you use any custom formula to determine which cell to format.
  • If you use a formula for conditional formatting that has mixed or absolute references, then conditional formatting might not work if you copy it to another cell.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


Conclusion

In this article, we have learned how to copy conditional formatting to another cell in Excel. I hope from now on you can copy conditional formatting to another cell in Excel easily. However, if you have any queries or recommendations about this article, please do leave a comment below. Have a great day!!!


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.
ASM Arman
ASM Arman

Abu Saleh Arman is a Marine engineer and Excel & VBA expert. He loves programming with VBA. He finds VBA programming a time-saving tool to manipulate data, handle files, and interact with the internet. He is very interested in Python, MATLAB, PHP, Deep Neural Networks, and Machine Learning, showcasing his diverse skill set. Arman holds a B.Sc in Naval Architecture & Marine Engineering from BUET, Bangladesh. However, he switched to a content developer, where he writes technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo