Google Sheets: How to Copy Conditional Formatting Rules

Yes. In Google Sheets, copy a cell or range with the conditional formatting you want, select the destination, then choose Edit > Paste special > Conditional formatting only. This copies the rules without replacing the destination’s values or formulas; afterward, check the rule’s applied range and any custom formula references.

How conditional formatting works in Sheets

Conditional formatting changes how cells look when specified conditions are met. A rule might color overdue dates red, highlight duplicate entries, or shade a row when its status is “Complete.” The formatting is not simply a permanent fill color: Sheets evaluates the rule against the cells in its applied range and updates their appearance when their contents change.

That distinction matters when copying. A cell can look red because a rule makes it red, even if its ordinary fill color is white. Copying the visible appearance is not always the same as copying the rule that creates it. If the goal is for the destination to respond to its own values in the same way, copy the conditional formatting rule, not just the current colors.

Rules are managed from Format > Conditional formatting. The Conditional format rules sidebar shows each rule, its condition or custom formula, and the range to which it applies. You can use that sidebar to inspect what was copied and edit the destination range or condition when needed.

Copy rules with Paste special

Paste special is the most direct choice when you want to transfer conditional formatting while leaving destination data alone. In a Google Sheets web browser, follow these steps:

Select a cell or range that already has the rule you want. If a rule applies to a larger range, selecting a representative cell within that range is often sufficient, but copying the full source range can make the intended pattern clearer.
Choose Edit > Copy, or press Ctrl+C on Windows or ChromeOS. On a Mac, press Command+C.
Select the destination cell or range. It can be on the same sheet or another sheet in the spreadsheet.
Choose Edit > Paste special > Conditional formatting only.
Open Format > Conditional formatting and confirm that the copied rule appears and applies to the intended destination range.

The key benefit is that this paste option does not overwrite the destination’s values, formulas, or ordinary cell formatting. For example, you can copy a rule that highlights late tasks in one project table to a second table that already contains different task names and dates. The destination data stays in place, while the conditional formatting is added.

If the destination already has conditional formatting, inspect the sidebar rather than assuming the new rule replaced the old one. Existing and copied rules can coexist, and overlapping rules may produce a result that is not obvious from the cell’s appearance alone. Remove or revise rules you no longer need.

Use Paint format for a broader formatting copy

The Paint format tool is useful when you want to copy a cell’s formatting more generally. Select the source cell, click the paint roller icon in the toolbar, and then click or drag across the destination cells. This is convenient for applying a consistent appearance to nearby cells, especially when you also want ordinary formatting such as font, borders, or number formats.

However, Paint format is not the clearest choice when the specific goal is to transfer conditional formatting rules and preserve everything else at the destination. It is intended to copy formatting, not just conditional formatting. Use Paste special > Conditional formatting only when you want that narrower operation. After using Paint format, check the result and the rules sidebar, particularly if the destination already has its own style or rules.

There is also a practical difference between a cell’s current look and its conditional format. If the source rule currently evaluates as false, the source may not show its conditional color at all. The rule can still be present. Inspect the sidebar to confirm the source rule before copying, rather than judging solely by the visible fill color.

Copying rules to another range or sheet

For a destination on the same sheet, select the target cells carefully. If the original rule applies to A2:A20 and you want the equivalent treatment for B2:B20, paste the conditional formatting onto B2:B20, then check the applied range. Do not assume that copying a single source cell will always produce the exact range shape you intend; the sidebar is the reliable place to verify it.

To copy a rule to another tab in the same spreadsheet, copy the source and select the destination cells on that tab before using the conditional-formatting-only paste option. If the rule uses a custom formula or refers to another sheet, check its references after pasting. A rule that points to a particular source range may continue to refer to that range, which might not be appropriate for the new tab.

If you want the entire sheet, including its data and layout, make a copy of the sheet tab instead. Click the tab’s dropdown arrow and choose Duplicate. This creates a sheet copy with its contents and formatting, including conditional formatting, and can be a practical way to preserve a template. It is a broader action than copying rules: the duplicated sheet also contains the source data and other sheet-level content. Review its rules and references before using it as a new working sheet.

Applied ranges and custom formulas

Every conditional formatting rule has an applied range. That range determines which cells are evaluated. When copying a rule, make sure the target range matches the layout you are formatting. A rule intended for a whole row may behave differently if it is pasted onto only one column, and an accidental range such as A2:A1000 can affect blank or unrelated cells.

Custom formulas commonly use dollar signs to control which references move. For instance, suppose a task table has task names in A2:A20, due dates in B2:B20, and status values in C2:C20. To color each row when its status is Late, apply the rule to A2:C20 and use this custom formula: =$C2="Late". The dollar sign fixes the status column at C, while the row number remains relative, so each row checks its own status. For a row where C2 contains Late, the formula evaluates to TRUE and the formatting applies to that row.

When a rule is copied, relative references may adjust in relation to the new location, while absolute references marked with dollar signs stay fixed. This can be helpful when adapting a rule to a neighboring table, but it can also create an unexpected reference. After copying a custom-formula rule, read the formula in the sidebar and compare it with the first cell of the applied range. If the rule should check a different column or sheet, edit it rather than relying on an automatic adjustment.

Choose the right copying method

The best method depends on what should change at the destination. Use conditional-formatting-only paste for rules alone, ordinary paste when you mean to copy cells and their contents, Paint format for broader cell styling, and Duplicate for a full sheet template. These actions are not interchangeable.

Rules only: Use Edit > Paste special > Conditional formatting only. Destination values and formulas are preserved.
Cell contents and formatting: Use ordinary copy and paste, then inspect the destination. This can bring over more than the rule, including values or formulas.
Visual styling: Use Paint format when you want a broader formatting copy, not just conditional formatting.
Whole-sheet starting point: Use Duplicate from the sheet tab’s dropdown menu, then adjust data and references.

Excel uses a similar concept of conditional formatting rules, but its interface and paste options are not identical to Google Sheets. Instructions such as Excel’s Home ribbon paths do not apply in Sheets. If you are following directions for a different spreadsheet program, use the Google Sheets menus named here and verify the rule in Sheets’ Conditional format rules sidebar.

Common problems and fixes

If the copied cells do not change appearance, first confirm that the rule was copied and that its condition is true for the destination data. A rule for dates before today will not highlight a destination cell containing a future date. Also confirm that the destination falls inside the rule’s applied range and that the rule’s condition matches the data type and values present there.

If cells show the wrong color, look for overlapping rules or a formula referencing the wrong row or column. Open Format > Conditional formatting, select the relevant rule, and inspect both its condition and range. A copied rule may be valid but still target an unintended range or continue to look at the source table.

If you accidentally replace data, undo immediately with Ctrl+Z on Windows or ChromeOS, or Command+Z on a Mac. That is one reason to choose the conditional-formatting-only paste option when preserving values is important. For a shared or important spreadsheet, make a copy or confirm that you can restore the previous version before making broad changes.

For large or complicated sheets, give rules clear conditions and keep applied ranges as specific as practical. Unnecessary overlapping rules make later troubleshooting harder. If a rule is no longer needed, remove it from the sidebar instead of layering another rule over it to mask the first one.

Frequently Asked Questions

Can I copy conditional formatting without copying the cell value?
Yes. Copy the source, select the destination, and choose Edit > Paste special > Conditional formatting only. This transfers the rules without replacing destination values or formulas.

Can I copy conditional formatting to another tab?
Yes, within the same spreadsheet. Copy the source, select cells on the other tab, and use the conditional-formatting-only paste option. Then inspect the applied range and any formula references in the rules sidebar.

Will copied rules update when the destination data changes?
Yes, if the rule applies to the destination cells and its condition is written to evaluate those cells. If a custom formula still refers to the source range, edit the formula so it checks the intended destination data.

Does Paint format copy conditional formatting?
Paint format is a general formatting tool, while conditional-formatting-only paste is the explicit option for transferring rules alone. If you use Paint format, check the destination appearance and rules sidebar to confirm it did what you intended.

Why does the copied rule color the wrong cells?
Common causes include an incorrect applied range, a relative formula reference that shifted, or another rule that overlaps the same cells. Open Format > Conditional formatting and review the range, formula, and other rules affecting the destination.

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.