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.
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.
- 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.
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.
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.
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
- How to Make Yes Green and No Red in Excel
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting Color 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
- How to Copy Conditional Formatting But Change Reference Cell in Excel
