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.

Table of Contents hide

Download Practice Workbook

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

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

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

Quick Notes

Conclusion

Download Practice Workbook

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

Copy Conditional Formatting.xlsx

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.

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.

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.

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.

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.

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.

Step 3:

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

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.

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