How to Copy Conditional Formatting Between Tabs — and to Another Spreadsheet
Google Sheets gives you several ways to copy conditional formatting, and every one of them has a side effect the interface never mentions. This guide walks through each method — what it actually does to the rules underneath — and then covers the harder problem: getting conditional formatting into a different spreadsheet cleanly.
Copying between tabs in the same file
Method 1: Paint format
Select a cell that carries the formatting, click the paint roller in the toolbar, then select the target range (on the same tab or another tab). This transfers formatting — including conditional formatting — to the target.
The side effects: paint format carries all formatting, so number formats, fonts, and borders come along with the rule, overwriting what the target had. And under the hood it doesn't extend the original rule — it creates new rule entries for the painted range. Do this a few times and the rules panel fills with near-identical copies of the same rule.
Method 2: Copy, then Paste special > Format only
Copy the source cells (Ctrl/Cmd+C), select the target, then Edit > Paste special > Format only (Ctrl/Cmd+Alt+V). Same result as paint format — conditional formatting included, static formatting included, and new rule entries created per paste.
Method 3: Recreate the rule with an added range
The cleanest native method: open Format > Conditional formatting, click the rule, and under Apply to range click the range field and add another range (the range picker accepts multiple ranges separated by commas — but note it only offers ranges on the current tab). For cross-tab copies you instead open the target tab and rebuild the rule by hand, copying the condition and colors field by field. Tedious, but it produces exactly one rule, with no static-formatting side effects.
Watch your custom formulas
Custom-formula rules are evaluated from the top-left cell of their range, with relative references shifting across the range. When a copy method places the rule on a range that starts at a different row or column, unanchored references now point somewhere else — the rule still “works,” but it tests different cells than the original. After any copy, re-read the formula against its new range, and anchor with $ what should not move.
Copying to another spreadsheet
Method 1: Copy the whole tab
Right-click the tab name and choose Copy to > Existing spreadsheet (or New spreadsheet). The copied sheet arrives in the destination file with its conditional formatting rules intact. This is the most reliable native route — but it copies the entire tab: data, formatting, everything. You then either keep the copied tab, or paint/paste its formatting onto your real target tab and delete it.
Method 2: Copy-paste cells across files
Copying a range and pasting it into another Google Sheets file (same browser, both in Google Sheets) recreates the conditional formatting rules on the pasted range in the destination. Combined with Paste special > Format only this can move formatting without moving data. The usual caveats apply doubled: new rule entries per paste, static formatting comes along, and custom-formula references shift relative to the paste position.
What you can't do natively
There is no native way to copy a rule itself — with its exact multi-range footprint — to another file, no way to copy rules to several tabs in one step, and no backup format you can save before restructuring a sheet. Once rules matter to you, that gap becomes the real problem.
Or copy rules as rules
Everything above works, and for a one-off copy it's fine. If you manage rules regularly, Format Rules Manager, a free Google Sheets add-on, copies rules as rules: pick a rule (or several), pick target tabs, and it appends each rule with its exact ranges after the target's existing rules — no static-formatting side effects, no accidental duplicates, undo included. For other files, it exports rules as JSON that you paste into the add-on's Import in the destination spreadsheet, with a preview that maps rules to tabs and validates everything first. The same JSON doubles as a backup of your formatting logic.
Frequently asked questions
Why did copying create duplicate rules?
Because paint format and paste special create a new rule entry for each paste rather than extending the existing rule's range. The duplicates behave identically at first, which is why nobody notices until the rules panel is a wall of copies. They can be consolidated: one rule can hold multiple ranges — see our cleanup guide.
Will my custom formula still work after copying?
It will still run — but check what it points at. Relative references shift with the rule's new range position; only $-anchored parts stay put. A rule copied to a range starting at a different row can silently test the wrong cells while looking correct.
Can I copy conditional formatting from Excel to Google Sheets?
Importing an .xlsx file converts most common conditional formatting (color scales and standard conditions) to Google Sheets rules, but complex Excel features (icon sets, data bars) have no Google Sheets equivalent and are dropped or approximated. After any import, open the rules panel — or a full inventory — and verify what survived.