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.
Copy Conditional Formatting to Another Sheet: 2 Ways
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.
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.
Read More: Conditional Formatting with Formula in Excel
Download Practice Workbook
You can download the free Excel template from here and practice on your own.
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 Make Yes Green and No Red in Excel
- How to Create a Rating Scale in Excel
- How to Use Conditional Formatting on Text Box in Excel
- How to Apply Borders in Excel with Conditional Formatting
- How to Apply Alignment in Excel Conditional Formatting
- How to Copy Conditional Formatting Color to Another Cell in Excel
- How to Copy Conditional Formatting with Relative Cell References in Excel
- How to Copy Conditional Formatting But Change Reference Cell in Excel