How to Copy Conditional Formatting to Another Sheet (2 Quick Methods)

If you need to use the same Conditional Formatting for several sheets, no need to apply the same Conditional Formatting repeatedly. Excel has features to copy the Conditional Formatting also. This article will show you two quick ways to copy Conditional Formatting to another sheet in Excel with easy steps.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.


2 Ways to Copy Conditional Formatting to Another Sheet

Let’s get introduced to our dataset first which represents some salespersons’ sales in different regions. Have a look that I have used Conditional Formatting to highlight the sales greater than $700,000.


1. Use Format Painter to Copy Conditional Formatting to Another Sheet

In this method, we’ll use the Format Painter command from the Clipboard section of the Home tab to copy Conditional Formatting to another sheet.

Steps:

  • Select the range where you’ve applied Conditional Formatting.
  • Then click the Format Painter command from the Clipboard group of the Home tab.

Soon after, a dancing rectangle will appear.

Use Format Painter to Copy Conditional Formatting to Another Sheet

  • Click on the sheet where you want to paste the Conditional Formatting.

I want to copy it to the February sheet.

Use Format Painter to Copy Conditional Formatting to Another Sheet

You will see a brush icon attached to your cursor.

  • At this moment, just click on the first cell of the range where you want to paste the Conditional Formatting.

Also, you can drag over the range to paste Conditional Formatting.

Use Format Painter to Copy Conditional Formatting to Another Sheet

Now see that the Conditional Formatting is copied to that sheet successfully.

Read More: How to Copy Conditional Formatting to Another Workbook in Excel


Similar Readings:


2. Apply Paste Special to Copy Conditional Formatting to Another Sheet

The Paste Special command has many applications. We can easily apply it to copy Conditional Formatting to another sheet too.

Steps:

  • Select the range from where you want to copy the Conditional Formatting.
  • Then simply copy it.
  • Later, click on the sheet where you want to paste.

Apply Paste Special to Copy Conditional Formatting to Another Sheet

  • Select the first cell of the range where you want to paste.
  • Right-click on your mouse and select Paste Special from the Context menu.

Apply Paste Special to Copy Conditional Formatting to Another Sheet

  • After the Paste Special dialog box appears, mark Formats from the Paste section.
  • Finally, press OK.

Apply Paste Special to Copy Conditional Formatting to Another Sheet

Then you will observe that Excel has copied the conditional formatting to the sheet.

Read More: How to Copy Conditional Formatting to Another Cell in Excel (2 Methods) 


Check for Issues While Copying Conditional Formatting to Another Sheet

You will get the wrong result in some cases while copying the Conditional Formatting to another sheet. One of the major issues is the reference problem.

For the following dataset, I have used a formula to highlight the sales greater than $700,000.

Check for Issues While Copying Conditional Formatting to Another Sheet

Here is the formula. Have a look that the formula is applied to Column D.

Check for Issues While Copying Conditional Formatting to Another Sheet

Then I’ve copied the Conditional Formatting to another sheet in Column E. And it is showing the wrong result.

Check for Issues While Copying Conditional Formatting to Another Sheet

The reason is- we’ve used absolute reference for Column D. For that, after copying to another column the formula is not syncing with the new column.

Check for Issues While Copying Conditional Formatting to Another Sheet

Solution:

  • Use relative reference before copying or rewriting the formula after copying.

Check for Issues While Copying Conditional Formatting to Another Sheet

Now see that we have got the correct output after copying.

And the formula has been changed automatically for Column E.


Conclusion

I hope the procedures described above will be good enough to copy the conditional formatting to another sheet in Excel. Feel free to ask any question in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo