How to Copy Conditional Formatting Rules to Another Worksheet/Workbook?

Conditional formatting is one of the most commonly used functions in Excel. Generally, conditional formatting can be used to make patterns and highlight the data based on the cell value. We can create conditional formatting based on the rules, such as cell value and its range. Creating conditional formatting for every need we have can be a time-consuming and lengthy process. So, we can copy the condition formatting that is already there into the same workbook or another workbook.

Read this tutorial to learn how to copy conditional formatting rules to another worksheet or workbook in Excel. We can complete this process using the format painter.

Copy Conditional Formatting Rules in Excel

Here we will first create a new conditional format and then use the format painter to complete the task. Let's see a simple process to know how we can copy conditional formatting rules to another worksheet or workbook in Excel.

Step 1

Let us consider any Excel sheet where the data in the sheet is similar to the data shown in the below image.

Now, to create conditional formatting for the number, select the data, then click on conditional formatting and select a new rule.

Select data > Conditional formatting > New rule

Then select custom from the list, enter the formula as =A2>300, click on format, then click on fill, select color, and click OK.

Custom > Formula > Format > Fill > Color > Ok

and the formatted sheet will be similar to the below image.

Step 2

Now to copy the condition formatting, select the range of cells where the formatting applies and click on the format painter.

Step 3

Then select the range of cells where you want to apply the format. You could have observed that the formatting applied to the new values is the same as the old. So, we need to change the references to make them accurate.

To change the reference, select the new data, then click on conditional formatting, click on manage rules, and select edit to rules.

Select data > Conditional formatting > Manage rule > Edit.

Then change the reference in the formula as shown in the below image.

Conclusion

In this tutorial, we used a simple example to demonstrate how you can copy conditional formatting in Excel.