ExcelInsider Team
Applying consistent conditional formatting across multiple sheets in Excel ensures uniform data visualization and saves time. Whether you’re highlighting top performers, flagging overdue tasks, or using color scales, Excel offers several methods to replicate conditional formatting across sheets.
In this article, we’ll explore various techniques to copy conditional formatting to another sheet in Excel, ensuring your formatting remains consistent throughout your workbook.
Steps to copy Conditional Formatting to another sheet in Excel:
➤ Press Alt + F11 to open the VBA editor.
➤ Insert a new module and paste the code:
Sub CopyConditionalFormatting()
   Sheets("Sheet1").Range("C2:C11").Copy
   Sheets("Sheet2").Range("C2:C11").PasteSpecial Paste:=xlPasteFormats
   Application.CutCopyMode = False
End Sub➤ Press F5 or click Run to execute the macro.
Copy Formatting with Format Painter
The Format Painter tool lets you copy formatting (including conditional formatting) from one range to another, even across different sheets.
Steps:
➤ Go to Sheet1 and select the range with the desired conditional formatting (e.g., C2:C11).
➤ On the Home tab, click Format Painter.
➤ Switch to Sheet2 and select the target range (e.g., C2:C11).
The conditional formatting will now be applied to the selected range on Sheet2.
Note:
This method works best when both sheets share a similar structure.
Transfer Formatting via Paste Special
Paste Special allows you to copy just the formatting from one range and apply it to another sheet.
Steps:
➤ On Sheet1, select the range with conditional formatting (e.g., C2:C11) and press Ctrl + C
➤ Go to Sheet2 and select the target range (e.g., C2:C11)
➤ Right-click and choose Paste Special.
➤ In the dialog box, select Formats and click OK.
The conditional formatting is now applied to the selected range.
Replicate Rules via Conditional Formatting Manager
This method gives you precise control over the formatting logic, especially for complex formula-based rules.
Steps:
➤ On Sheet1, go to Home >> Conditional Formatting >> Manage Rules.
➤ In the Conditional Formatting Rules Manager, select the rule and click Edit Rule.
➤ Copy the formula shown in the rule settings.
➤ Switch to Sheet2, select the target range, and go to Conditional Formatting > Manage Rules.
➤ Click New Rule, then choose Use a formula to determine which cells to format.
➤ Paste the formula.
➤ Click Format, select a fill color (e.g., green), and press OK.
The rule will now apply the same conditional logic to Sheet2.
Duplicate Entire Sheet to Preserve Formatting
If you want to replicate all formatting, including conditional rules, copying the entire sheet is the fastest option.
Steps:
➤ Right-click the Sheet1 tab at the bottom.
➤ Select Move or Copy.
➤ In the dialog box, check Create a copy and choose where to place the copy.
➤ Click OK.
A new sheet with all of Sheet1’s formatting is created, including conditional formatting.
Note:
This is useful when building a new sheet with an identical structure.
Automate Using VBA Code
If you regularly apply conditional formatting between sheets, VBA can automate the process for you.
Steps:
➤ Press Alt + F11 to open the VBA editor.
➤ Insert a new module and paste the code:
Sub CopyConditionalFormatting()
   Sheets("Sheet1").Range("C2:C11").Copy
   Sheets("Sheet2").Range("C2:C11").PasteSpecial Paste:=xlPasteFormats
   Application.CutCopyMode = False
End Sub➤ Press F5 or click Run to execute the macro.
Conditional formatting from Sheet1 is now applied to the specified range on Sheet2.
Note:
You can change the sheet names and ranges in the code as needed.
Frequently Asked Questions
Can I copy conditional formatting to non-adjacent ranges?
Yes. After copying the formatting using Format Painter or Paste Special, you can select multiple non-adjacent ranges by holding down the Ctrl key while selecting.
Will copying conditional formatting overwrite existing rules?
Yes. Applying new conditional formatting to a range will replace any existing rules. It’s advisable to review existing rules before applying new formatting.
Can I copy conditional formatting to a different workbook?
Yes. Ensure both workbooks are open in the same Excel instance. Then, use Paste Special or Format Painter to copy the formatting between workbooks.
Wrapping Up
In this tutorial, we’ve explored multiple methods to copy conditional formatting to another sheet in Excel, including using Format Painter, Paste Special, the Conditional Formatting Rules Manager, duplicating entire sheets, and VBA automation. Each method serves different scenarios, ensuring flexibility and efficiency in managing your Excel workbooks.
Feel free to download our sample file to practice these techniques and share your feedback.
