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.
- 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.
- Excel Conditional Formatting Based on Date Range
- Pivot Table Conditional Formatting Based on Another Column (8 Easy Ways)
- Conditional Formatting with INDEX-MATCH in Excel (4 Easy Formulas)
- How to Do Conditional Formatting Highlight Row Based On Date
- Highlight Row Using Conditional Formatting (9 Methods)
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.
- 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.
- 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.
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.
- How to Apply Conditional Formatting to Multiple Rows (5 Ways)
- Do Conditional Formatting with Multiple Criteria (11 Ways)
- Conditional Formatting on Text that Contains Multiple Words in Excel
- How to Change Text Color with Formula in Excel (2 Methods)
- Conditional Formatting Entire Column Based on Another Column(6 Steps)