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

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.


Download Practice Workbook

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


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

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 the 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


Similar Readings:


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

Alternatively, we can also use the 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.

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

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo