Copy Conditional Formatting to Other Cells in Excel

Yes. In Excel for Microsoft 365 on Windows, select a cell with the conditional formatting you want, choose Home > Format Painter, then drag across the destination cells. You can also copy the source and use Home > Paste > Paste Special > Formats; check the resulting rules if the copied formatting behaves differently than expected.

What copying conditional formatting actually does

Conditional formatting changes a cell’s appearance when a rule’s condition is met. For example, a rule can color negative numbers red, highlight dates that are overdue, or add data bars to a range. The displayed appearance is calculated from the cell’s value and the rule; it is not simply a permanent fill color.

When you copy a formatted cell, Excel can copy its conditional formatting rule along with other cell formatting. The rule may also adjust its cell references for the new location. That is usually useful when extending a pattern, but it can produce an unexpected result if the rule uses references that should stay fixed.

It is also important to distinguish copying a cell’s formatting from copying only its conditional formatting. Format Painter and Paste Special > Formats can transfer other formatting too, such as number formats, borders, fonts, and ordinary fill colors. If those destination details must stay unchanged, edit or extend the conditional formatting rule instead of copying all formats.

Use Format Painter for a quick copy

Format Painter is convenient when you want to copy the appearance and formatting rules from one cell or range to another. In Excel for Microsoft 365 on Windows:

Select the source cell or range that has the conditional formatting.
On the Home tab, click Format Painter in the Clipboard group.
Drag across the destination cell or range. For a single destination cell, click it.
Check a few destination cells to confirm the rule responds to their values as intended.

If you need to apply the same formatting in several places, double-click Format Painter to keep it active between selections. Press Esc when you are finished. The tool copies formatting, not the source cell’s value or formula. However, it can also copy non-conditional formatting, so a destination with carefully set number formats or borders may change.

On Excel for Mac, Format Painter is also available on the Home tab. Ribbon layout can differ slightly by version, but the basic process is the same: select the source, activate Format Painter, and select the destination. Excel for the web also offers Format Painter, though some advanced rule-management options may be more limited than in the desktop application.

Use Paste Special to copy formats

Paste Special is useful when you prefer a menu-based method or already have the source cell copied. Select the source and press Ctrl+C on Windows. Select the destination, then choose Home > Paste > Paste Special > Formats. This pastes formatting without replacing the destination’s values or formulas.

On Windows, Ctrl+Alt+V opens the Paste Special dialog after copying. Choose Formats and confirm. On a Mac, Command+C copies the source and Command+Control+V opens Paste Special in supported desktop versions; the exact dialog shortcuts can depend on the keyboard and Excel version, so the Home tab’s Paste Special menu is a reliable alternative. Menu labels and ribbon placement can vary slightly between releases.

Formats is broader than conditional formatting alone. It can carry number formats, fonts, borders, alignment, and ordinary fills as well as conditional formatting. If the source uses a date format and the destination should remain a percentage, for instance, Paste Special > Formats may change more than you intended. Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the paste has altered unwanted formatting, then use the rule-management approach below.

Copy only the conditional formatting rule

If you want to preserve the destination’s other formatting, use the Conditional Formatting Rules Manager rather than a general formatting copy. Select a cell in the relevant range, then choose Home > Conditional Formatting > Manage Rules. In the manager, use the Show formatting rules for list to inspect rules for the current selection or the whole worksheet.

For a rule that should cover a larger continuous range, inspect its Applies to field and expand the range to include the additional cells. The field identifies where that rule is active; changing it can be cleaner than creating a separate copy of the rule for each block. Select Apply or OK to save the change, and confirm that the rule is evaluated against the intended cells.

For a separate destination where the existing rule should remain independent, select the destination range and create a matching rule through Home > Conditional Formatting. Re-enter the condition and set the desired formatting. This takes longer than Format Painter, but it avoids unintentionally changing fonts, borders, or number formats. It also makes the rule’s intended range easier to understand later.

Understand how cell references shift

Rules based on formulas are especially sensitive to relative and absolute references. A relative row or column reference can adjust when the rule is copied or its Applies to range changes. A dollar sign locks that part of a reference: $B2 locks column B but allows the row number to adjust, while $B$2 locks both the column and row.

For example, assume a table uses A2:D20, with each record on one row and its status number in column B. To highlight an entire row when the status number is negative, create a formula-based rule for A2:D20 using =AND($B2<>"",$B2<0). The dollar sign before B keeps the test in column B; the unanchored row number lets Excel test B3 for row 3, B4 for row 4, and so on. If B2 contains -12, the condition is true for the first data row; if B3 contains 5, it is false for the second.

The formula is interpreted relative to the top-left cell in the rule’s Applies to range. If you copy a rule to a different starting row, Excel may adjust relative references to preserve the pattern. That adjustment is often correct for row-by-row checks, but not if every destination should refer to one fixed cell. In that case, use absolute references such as $B$2, or inspect the formula after copying.

Choose a method for the situation

The best method depends on whether you want all formatting copied, only a rule copied, or the same visual result applied to a new range.

Format Painter: Best for a fast copy from a source cell or range. It can copy other formatting along with conditional formatting.
Paste Special > Formats: Best when you want a menu-controlled paste that leaves values and formulas alone. It still copies more than conditional formatting.
Manage Rules: Best for changing the range covered by a rule or checking formulas and rule order without replacing other destination formatting.
Create a new rule: Best when the destination needs its own independent condition or should keep all existing cell formatting.

For example, to apply a red warning to an adjacent month’s figures, Format Painter may be enough if the source and target layouts match. To highlight a different column based on a status field, a new formula-based rule is usually clearer than copying a rule whose references may point to the wrong place.

Common problems and how to fix them

The copied cells look the same but do not respond to their own values. Open Manage Rules and check the formula and Applies to range. A locked reference may point every cell to one value, or a relative reference may have shifted in a way you did not intend.

Unexpected fonts, borders, or number formats changed. This is a sign that a general formatting method copied more than the conditional rule. Undo if possible, then create the rule for the destination or adjust the existing rule’s Applies to range.

The destination looks unchanged. The condition may not be true for the destination’s current value. Test with a value that should meet the rule, and check whether another rule takes priority or whether the destination is included in Applies to. Also check that the chosen rule format is visually distinct from the cell’s current appearance.

Some cells behave differently from others. Several rules may apply to the same cells, or the range may contain multiple rules with different formulas. In Manage Rules, inspect the rules for the worksheet and their order. If a rule has Stop If True selected, later rules may not be evaluated when that rule is true.

Copying a cell seems to bring the rule along again. Repeated copying can create overlapping or duplicate rules. Review Manage Rules and remove redundant entries carefully; deleting the wrong rule can affect many cells. Before changing a complex workbook, consider saving a separate copy so you can restore the original if needed.

Check the result and keep rules manageable

After copying or editing a rule, test both a value that should trigger it and one that should not. Then open Home > Conditional Formatting > Manage Rules and confirm that the intended range appears in Applies to. If the worksheet has many rules, switch the manager’s Show formatting rules for setting to the current selection to focus on the rules affecting those cells.

Clear rule names and sensible ranges make later maintenance easier. When a single rule can cover the entire intended range, extending its Applies to range may be easier to manage than maintaining many near-identical copies. Conversely, separate rules are appropriate when different areas need different conditions or formatting. Avoid deleting rules just because their formatting currently looks alike; their formulas or target ranges may differ.

Frequently Asked Questions

Can I copy conditional formatting without copying the cell value?
Yes. Format Painter copies formatting, not values. Paste Special > Formats also pastes formatting without replacing destination values or formulas, although both methods can copy more than conditional formatting.

Why does copied conditional formatting refer to a different cell?
Relative references normally adjust as a rule is copied or applied elsewhere. Check the formula and use dollar signs to lock the row, column, or both where needed.

Can I copy conditional formatting to a nonadjacent range?
Yes. Use Format Painter on each destination, or select the intended range in the rule manager and adjust Applies to. For separate areas, verify the final ranges and formulas individually.

Will Paste Special > Formats copy only conditional formatting?
No. It can also copy fonts, borders, number formats, alignment, and ordinary fills. Use Manage Rules or create a rule directly when other destination formatting must stay untouched.

Can I copy conditional formatting between worksheets?
Yes. Copy the source formatting and paste it into the destination sheet, then inspect the rule references and Applies to range. Relative references and sheet-specific ranges may need adjustment after the copy.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.