How to Copy a Conditional Formatting Formula in Excel

Formulas & functions · Updated November 19, 2026

To copy a conditional formatting formula in Excel, copy a cell with the rule and use Home > Paste > Paste Special > Formats, or apply the rule with Format Painter. If you need to reuse the formula without copying other formatting, edit the rule under Home > Conditional Formatting > Manage Rules and check its Applies to range and cell references.

Choose the right way to copy the rule

The best method depends on whether you want to copy a whole cell’s appearance or just reuse a conditional formatting rule. Excel keeps conditional formatting rules separately from the value or formula in a cell, so copying a cell can bring along more than the conditional rule.

  • Use Format Painter when you want to copy the conditional formatting and other cell formatting, such as font, fill, borders, and number format.
  • Use Paste Special > Formats when you want formatting but do not want to replace the destination’s values or formulas. It can also copy ordinary formatting, not only conditional formatting.
  • Edit the rule’s Applies to range when the same rule should cover a larger or different range and you do not need a separate copy of the rule.
  • Create a new formula rule when the destination needs a different formula, reference, or formatting outcome.

Before copying, select a cell that actually has the conditional formatting rule. A cell may look colored because of a manually applied fill rather than a rule; copying it will not reproduce a conditional formatting rule that is not there.

Copy conditional formatting with Paste Special

In Excel for Microsoft 365 on Windows, copying a source cell and pasting its formats is a straightforward way to transfer a rule while retaining the destination’s contents. The destination cells can be a single cell or a range. If the copied rule uses relative references, Excel may adjust them to suit the new location, just as it adjusts references when copying formulas.

  1. Select a source cell that has the rule, then press Ctrl+C.
  2. Select the destination cell or range.
  3. Choose Home > Paste > Paste Special > Formats. In the Paste Special dialog, choose Formats and select OK if the dialog appears.
  4. Check the destination cells and, if needed, open Home > Conditional Formatting > Manage Rules to inspect the copied rule.

Paste Special > Formats does not replace the destination’s values or formulas, but it can change other formatting there. For example, it may copy the source cell’s number format, font, borders, and fill as well as its conditional formatting. If that is not acceptable, use the rule manager to adjust or recreate the conditional rule instead.

Format Painter is another option: select the source cell, choose Home > Format Painter, then drag or select the destination. It is convenient for nearby cells, but it also paints other formatting. If you double-click Format Painter, you can apply it to more than one destination; press Esc when finished.

How formula references change when copied

A formula-based conditional formatting rule is evaluated for each cell in its Applies to range. Relative, absolute, and mixed references determine which cells the formula checks. The formula is interpreted in relation to the top-left cell of the Applies to range, so the same formula can behave differently if that range starts somewhere else.

For example, with sales amounts in B2:B20 and a threshold in F1, suppose the rule applies to B2:B20. The formula =B2>$F$1 compares each row’s sales value with the fixed threshold. The B2 reference is relative, so it can change to B3, B4, and so on for other rows. The dollar signs in $F$1 keep the threshold reference fixed.

With task names in A2:A20 and statuses in C2:C20, suppose the rule applies to A2:A20. The formula =$C2="Late" highlights a task name when the status in column C on the same row is Late. The dollar sign before C locks the column, while the row number remains relative. If you extend the rule to a wider range such as A2:C20, however, the relative references will be evaluated from the cells in that range; verify the result rather than assuming the original layout still fits.

Reference behavior is a frequent source of surprises. A formula that works for the first row can color every row incorrectly if its row or column is locked when it should move, or left relative when it should stay fixed. Use F4 while editing a reference in the formula bar to cycle through relative and absolute reference styles, or type the dollar signs yourself.

Copy a rule by changing its Applies to range

If your goal is to make an existing rule cover more cells, changing its range is often cleaner than copying and pasting. This avoids creating a second rule that does the same thing. In Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > Manage Rules. Set the Show formatting rules for list to the relevant worksheet if the rule is not visible, select it, and edit the Applies to field.

For instance, if a rule currently applies to =$B$2:$B$20 and should also cover B21:B40, you can change the range to include all intended cells. Be careful not to remove a separate range that the rule already covers. When one rule applies to multiple separated areas, the field can contain multiple ranges; inspect it before replacing its contents.

Editing the range changes where that rule applies, not the rule’s formula or formatting. If the new range starts in a different row or column, check whether relative references still point to the intended cells. A rule designed for column B may need a revised formula if it is applied to a different column.

Create a separate formula rule when needed

When the destination needs different logic, create a new rule instead of copying blindly. Select the target range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula, choose Format to set the appearance, and select OK. Make sure the formula is written for the top-left cell of the selected range.

For example, with duplicate product codes in A2:A100 and a rule applying to that same range, the formula =COUNTIF($A$2:$A$100,A2)>1 identifies codes that appear more than once. The fixed range checks all listed codes, while A2 changes for each cell being evaluated. If you apply this rule to a range beginning at A3 instead, adjust the relative reference so it matches that range’s first cell.

In Excel’s rule manager, select a rule and choose Edit Rule to revise its formula or formatting. The manager also lets you review rule order and the Applies to range. If a copied rule is not producing the expected result, inspect these settings before building another copy; an existing rule may already cover the destination.

Check rule order and overlapping ranges

More than one conditional formatting rule can apply to the same cell. When rules overlap, their order can affect the result, especially if two rules set the same format property. In Manage Rules, use the move-up and move-down controls to change priority. The Stop If True option can prevent lower-priority rules from being evaluated when a higher-priority rule is true; use it only when that stopping behavior is intended.

Copying or pasting formats can create additional rules or expand existing coverage, depending on the source, destination, and existing rules. A workbook with many overlapping or duplicated rules can be difficult to maintain. If a cell has an unexpected color, select it and inspect Manage Rules with the worksheet’s rules displayed. Check every rule whose Applies to range includes that cell, not just the one you most recently copied.

Also confirm that the rule’s formula returns the condition you expect. For example, text comparisons can be affected by extra spaces in the underlying data, and a date that looks like a date may be stored as text. Conditional formatting changes how a cell appears; it does not correct the cell’s stored value.

Excel for Mac and Excel for the web

On Excel for Mac, the same general approaches apply: use Format Painter, paste formats, or manage the conditional formatting rule. Ribbon labels and dialog layouts can vary slightly by release, so look under Home for Conditional Formatting and Paste options. On Mac, the common copy shortcut is Command+C rather than Ctrl+C; the exact Paste Special shortcut and dialog controls can also differ by version.

Excel for the web has conditional formatting and paste options, but its interface and available rule-management controls can differ from the Windows desktop app. If you cannot find a particular rule-editing control in the browser, open the workbook in the desktop app and use Home > Conditional Formatting > Manage Rules. Avoid assuming a Windows keyboard shortcut works identically in a browser or on a Mac.

Frequently Asked Questions

Can I copy only the conditional formatting rule, not the cell’s other formatting?
Paste Special > Formats can transfer other formatting too. To avoid changing the destination’s font, borders, or number format, create or edit the rule in Manage Rules and set its Applies to range and formula deliberately.

Why does the copied rule highlight the wrong cells?
The formula may use relative or absolute references that do not match the destination. Check which cell is at the top-left of the Applies to range, then adjust row and column dollar signs so each evaluation checks the intended cell.

Does Format Painter copy conditional formatting?
Format Painter can copy conditional formatting along with other formatting from the source. Because it copies more than the rule alone, inspect the destination if you need to preserve its existing appearance or rules.

Can I copy a conditional formatting rule to another worksheet?
You can copy a formatted cell to another worksheet or create a corresponding rule there. Afterward, check the formula references and Applies to range; references that made sense on the source sheet may not point to the intended cells on the destination sheet.

How do I remove a copied rule?
Select the affected cells and choose Home > Conditional Formatting > Clear Rules. Choose Clear Rules from Selected Cells to remove rules from the selection, or use Manage Rules to delete a particular rule. Clearing rules removes conditional formatting, not the cell contents.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.