How to Copy Conditional Formatting Rules in Excel

The fastest way to copy conditional formatting rules in Excel is Format Painter. Click a cell that has the rule, click Format Painter on the Home tab, then click or drag over the cells that should get the same rule. To copy formatting only, without overwriting values, copy the cell and use Paste Special > Formats (Ctrl + Alt + V, then T). To extend a rule to more rows without creating duplicates, edit its Applies to range in Conditional Formatting > Manage Rules. Here’s each method, plus how to copy rules to another sheet or workbook. If you haven’t created a rule yet, see how to use conditional formatting in Excel first.

Method 1: Format Painter

1. Click a cell that has the conditional formatting.
2. Go to Home > Format Painter (the paintbrush). The pointer turns into a brush.
3. Click a cell, or drag over a range, to apply the formatting.

Double-click Format Painter to keep it switched on, so you can paint several separate ranges. Press Esc to stop. Format Painter copies all formatting (fonts, borders, number formats and fills) as well as the conditional rules. To copy a plain fill without any rule, Paste > Formatting is the other option covered in coloring Excel cells by hand.

Method 2: Paste Special > Formats

1. Select the cells with the rule and press Ctrl + C.
2. Select the destination range.
3. Press Ctrl + Alt + V, choose Formats and click OK. Or right-click and choose the Formatting paste icon.

This is handy for large or distant ranges that are awkward to drag over. The other paste choices are explained in copying and pasting data into Excel.

Method 3: Extend the rule with Applies to (cleanest)

Copying and pasting can create many near-identical rules, which slows large workbooks down. Changing the range a rule covers keeps everything in one rule.

1. Go to Home > Conditional Formatting > Manage Rules.
2. Set Show formatting rules for to This Worksheet.
3. In the Applies to box for the rule, change the range, for example from =$A$2:$F$50 to =$A$2:$F$500. To add a separate area, type a comma and the second range.
4. Click Apply, then OK.

Duplicate a rule and change it

To make a similar rule, such as one for Late as well as Done, select the rule in Manage Rules and click Duplicate Rule. Then click Edit Rule and change the text, color or formula.

How formula rules change when copied

Formula rules are written for the top-left cell of the Applies to range, and they adjust like normal formulas: Mixed references like $A2 are what let a single rule highlight differences in two Excel columns on every row without rewriting it.

• =$C2="Done": the column is locked, so every cell in the row checks column C. This is what you want for coloring whole rows.
• =C2="Done": nothing is locked, so each cell checks the cell in its own position.
• =$C$2="Done": fully locked, so every cell checks C2 only.

If a copied rule colors the wrong cells, check the dollar signs; they work the same way as when you keep one cell constant in a formula.

Copy rules to another sheet

• Format Painter: Click the source cell and click Format Painter, then switch to the other sheet and click or drag over the cells.
• Paste Special: Copy the source cells, switch sheets, and use Paste Special > Formats.

If the rule refers to a cell on the original sheet (such as a target value in Sheet1!$H$1), the copied rule still points there. Edit it in Manage Rules if the new sheet has its own target cell.

Copy rules to another workbook

1. Open both workbooks.
2. Use Format Painter or Paste Special > Formats across the two windows.
3. Open Manage Rules in the new workbook and check each formula. References to cells in the old workbook may need editing.

To bring across an entire layout with all its rules, right-click the sheet tab, choose Move or Copy, pick the other workbook, and tick Create a copy. Then replace the data. Pictures of the Move or Copy dialog are in copying a sheet to another workbook.

Use a table so rules extend automatically

Press Ctrl + T to turn your range into an Excel table before adding rules. When you add new rows at the bottom of the table, the conditional formatting extends to them automatically, so there’s nothing to copy.

Clean up duplicate rules

Inserting rows, sorting and pasting can split one rule into many fragments with almost identical ranges. To tidy up:

1. Open Manage Rules for This Worksheet.
2. Keep one copy of each rule, and widen its Applies to range to cover the whole area.
3. Delete the duplicates.

Or select the area, choose Clear Rules > Clear Rules from Selected Cells, and create one clean rule.

On a Mac, in Excel for the web and in Google Sheets

• Excel for Mac: Format Painter and Manage Rules work the same way. Open Paste Special with Ctrl + Cmd + V, or with Edit > Paste Special.
• Excel for the web: Format Painter is on the Home tab. Rules appear in a side pane, where you can edit the range a rule applies to.
• Google Sheets: Use the Paint format brush in the toolbar, or paste with Ctrl + Alt + V (format only). Edit ranges under Format > Conditional formatting.

Troubleshooting

Format Painter overwrote my number formats and borders

Format Painter copies everything. Undo with Ctrl + Z, then use Method 3 to copy just the rule.

The copied rule doesn’t color anything

The formula probably points at the wrong row or column. Check the formula against the first cell of Applies to.

Related guides

• How to change cell color based on text
• How to color cells based on value
• How to make a cell turn a color with a formula
• How to make cells change color based on date

Summary

1. Quick copy: Format Painter.
2. Formats only: Paste Special > Formats.
3. Cleanest: widen Applies to in Manage Rules.
4. Check the dollar signs in formula rules after copying.