Download Excel Workbook
Watch our free training video on How to Copy Conditional Formatting in Excel:
Conditional formatting is a feature that allows you to apply formatting to cells based on specified conditions. Instead of manually changing the formatting of cells, conditional formatting enables you to set rules that automatically determine how cells should be formatted based on their content or values.
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!
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.
