How to Copy and Paste Conditional Formatting in Excel

When we have the same type of dataset in our sheet or workbook and if we want to apply the same conditional formatting on every dataset then no need to create the same conditional formatting again and again. We can easily copy the conditional formatting just by following some easy tricky ways. Here, we are going to demonstrate 3 easy methods to copy and paste the conditional formatting in Excel.

Table of Contents hide

Download Practice Workbook

3 Easy Ways to Copy and Paste Conditional Formatting in Excel

1. Using Format Painter to Copy and Paste Conditional Formatting Including Colors

2. Using Paste Special to Copy and Paste Conditional Formatting in Excel

3. Using Rules Manager to Copy and Paste Conditional Formatting in Excel

Conclusion

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Copy and Paste Conditional Formatting.xlsx

3 Easy Ways to Copy and Paste Conditional Formatting in Excel

First of all, get introduced to the dataset that we’ll use for our methods. It contains some students’ obtained scores in two subjects. Look, Column C has conditional formatting to highlight the scores greater than 80. Our target is to copy the same conditional formatting for Column D.

1. Using Format Painter to Copy and Paste Conditional Formatting Including Colors

In our very first method, we’ll learn how to copy and paste conditional formatting with colors in Excel. Excel has a useful command named Format Painter that can be used for copying any kind of formatting. We’ll use it here.

Steps:

Select any cell from the range where conditional formatting is remaining.
Then go to the Home ribbon and click on the Format Painter icon.

Soon after, you will see that your cursor has changed to a painter icon with a plus sign.

At that moment, just left-click your mouse and drag the cursor along the cells where you want to paste the conditional formatting. (For our dataset the range is D5:D11)

Now see, the conditional formatting has been copied to the new column including the highlight color.

Similar Readings

2. Using Paste Special to Copy and Paste Conditional Formatting in Excel

The Paste Special tool of Excel is a very helpful tool that can do a wide range of tasks. This tool can also copy and paste the conditional formatting too. Let’s see how to do it.

Steps:

First, select any cell from the range where conditional formatting exists and then simply copy it by pressing Ctrl + C.

Then select the range where you want to apply conditional formatting and then right-click your mouse.
Choose Paste Special from the context menu.

After appearing the Paste Special dialog box, mark Formats from the Paste section.
Finally, just press OK.

Or you can directly choose the Formats option from the Paste Options of the context menu, that will do the same thing.

See, it has successfully copied the conditional formatting.

3. Using Rules Manager to Copy and Paste Conditional Formatting in Excel

We can duplicate any rule of conditional formatting from one range to another using the rules manager of conditional formatting. The plus point is you can modify anything of the rule before applying which is not possible for the previous two methods.

Steps:

Select any cell from the range where conditional formatting is remaining.
Next, click as follows: Home > Conditional Formatting > Manage Rules.

Later, select the specific rule and press Duplicate Rule.

After a while, you will get the duplicate one, click on the upside arrow icon to choose the new range.

You can also change the highlight color and formula from here.

Select the new range and hit the Enter button.

Finally, click Apply button and then press OK.

Now see the same rule has been applied to the new range.

Conclusion

That’s all for the article. I hope the above procedures will be good enough to copy and paste the conditional formatting in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit our site to explore more.