How to Copy Conditional Formatting in Excel

In this article, you’ll learn how to copy conditional formatting in Microsoft Excel. This will enable you to copy the highlighted (highlighted due to conditional formatting) columns from one range to another.

Let’s take an example:-

We have sales data in which we have highlighted the top 10 sales by using the conditional formatting. We wish to apply the same formatting in different range.

Follow below given steps:-

Select the range B2:B21

Go to Home Tab > Styles > Conditional Formatting
Click on Conditional Formatting

From the drop down list click on Manage rules
Click on Edit

Copy formula and click OK

Paste formula in the sheet
=$E2>=LARGE($E$2:$E$21,10)

Now use Format Painter:-

Select the range of first data
Go to Home tab > Format painter
Then paste in second range where we want to apply conditional formatting

Cells will get highlighted, but these are not top 10 sales
Go to Conditional formatting > Click on Manage rule
Manage rule dialog box will appear

Click on Edit rule

Change formula :- =$E2>=LARGE($E$2:$E$21,10)
Click on OK

Click on OK
Click on Apply and click OK

Top 10 sales will get highlighted, in this way we can copy the conditional formatting from one place to another place in Microsoft Excel.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com