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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

2 Ways to Copy Conditional Formatting to Another Sheet

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

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

Check for Issues While Copying Conditional Formatting to Another Sheet

Conclusion

Download Practice Workbook

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

Copy Conditional Formatting.xlsx

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.

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

I want to copy it to the February 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.

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

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.

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.

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

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

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.

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

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

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.

Solution:

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

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.