How to Copy Conditional Formatting in Excel – 3 Quick & Easy Methods

Once you apply conditional formatting to a cell, you may want to apply the same formatting elsewhere in the spreadsheet. This can be achieved by following the methods below to copy conditional formatting in Excel.

Download the Excel Workbook below to follow along and understand How to Copy Conditional Formatting in Excel – download excel workbookCopy-Conditional-Formatting.xlsx

 

Method 1 – Using Format Painter

Suppose you have details for various projects mentioned in a data table and your objective is to modify the font color to red for projects categorized as high-risk. Conditional formatting is already applied in the first row, you need to copy conditional formatting to all the rows.

This can be achieved using the format painter in Excel. The Format Painter is a versatile tool that allows you to copy formatting from one cell and apply it to another. Here’s how you can use Format Painter to copy conditional formatting –

STEP 1: Select the range of cells with the desired conditional formatting. Here, it is A2:E2.

STEP 2: Go to the Home tab and click on the Format Painter icon (looks like a paintbrush).

STEP 3: Click on the range of cells where you want to apply the formatting.

The formatting will be copied to the selected area.

 

Method 2 – Using Paste Special

Paste Special allows you to customize how the copied data is pasted into a new location. Instead of a simple copy-and-paste, Paste Special provides various options for pasting such as values, formulas, formats, validation, and more.

Here’s a step-by-step guide-

STEP 1: Select the range of cells with the desired conditional formatting. Here, it is A2:E2.

STEP 2: Go to Home > Copy.

STEP 3: Highlight the range of cells where you want to apply the formatting.

STEP 4: Right-click on the selected area, and select Paste Special from the menu.

STEP 5: In the Paste Special dialog box, select Formats and click OK.

The formatting will be copied to the selected area.

 

Method 3 – Duplicate Rule

This method involves duplicating an existing conditional formatting rule within the Conditional Formatting Rules Manager –

STEP 1: Select the range of cells with the desired conditional formatting. Here, it is A2:E2.

STEP 2: Go to the Home tab, click on Conditional Formatting, and select Manage Rules.

STEP 3: Select the rule you want to duplicate and click Duplicate Rule.

STEP 4: Select the range of cells where you want to apply the duplicated rule under Applies to section for the new rule created.

The same formatting will be applied to the output range.

 

Conclusion

In conclusion, mastering the art to copy conditional formatting in Excel is a valuable skill. Whether using the Format Painter for a quick and visual application,using Paste Special for controlled copying, or duplicating rules through the Conditional Formatting Rules Manager, these methods empower users to seamlessly copy conditional formatting in Excel.

Click here to know how to copy conditional formatting in Excel!