Excel crashes copying cells with conditional formatting

It's infuriating when Excel just dies for no obvious reason.

You copy a few cells with conditional formatting from one workbook to another, and boom — Excel freezes, then crashes. No warning, no error code, just a lost afternoon. I've fixed this for dozens of users, and the culprit is almost always corrupted or overly complex conditional formatting rules that the destination workbook can't handle.

The fix — do this first

Don't bother closing and reopening Excel a dozen times. It rarely helps. Here's what works:

1. Paste as values only. Right-click the destination cell, select Paste Special > Values (or press Ctrl+Alt+V, then V, then Enter). This strips all formatting — including the broken conditional formatting — and pastes just the data.
2. If you need the formatting too, copy the source sheet entirely instead of single cells. Right-click the sheet tab, choose Move or Copy, and check Create a copy. This avoids the cross-workbook conditional formatting crash because Excel copies the entire sheet's rules in one go.
3. If you're stuck and the source workbook itself is crashing, open it in Safe Mode: hold Ctrl while starting Excel, then copy from there. Safe mode disables add-ins and some formatting engine features that sometimes trigger the crash.

Why this happens

Conditional formatting rules are stored per worksheet, not per cell. When you copy a cell (or range) from one workbook to another, Excel tries to merge the source formatting rules into the destination workbook's rule table. If those rules reference named ranges, external workbook links, or formulas that don't exist in the destination, Excel's formatting engine chokes and dies. This is especially common in Excel 2019 and Excel 365 with the newer dynamic array formulas — those spill ranges don't translate well when copied between workbooks.

Another frequent trigger: conditional formatting rules that reference entire columns (like =$A:$A). Copying just a few cells from that column brings the whole column rule along for the ride, which the destination workbook's rule evaluator can't always handle.

Less common variations

Variation 1: Crash only when pasting to a specific workbook

One workbook is fine, the other crashes. Check the destination workbook for corrupted conditional formatting already in place. Go to Home > Conditional Formatting > Manage Rules. If you see any rules with #REF! errors or blank ranges, delete them. Those are time bombs.

Variation 2: Crash after pasting, but not immediately

You paste successfully, then Excel crashes when you try to edit something else. This usually means the paste partially succeeded — the condition is in the rule table but broken. Open the Rules Manager, find the new rule (it'll show the source workbook's name in the rule description), and delete it.

Variation 3: Crash on copy, not paste

Rare, but happens. This often points to corrupted themes or add-ins. Try disabling COM add-ins: File > Options > Add-ins > Manage: COM Add-ins > Go, uncheck all, restart Excel.

How to prevent it from happening again

Three habits that'll save you:

• Keep conditional formatting ranges small. Avoid applying rules to entire columns or rows. Use specific ranges like $A$1:$A$100. Smaller rules copy faster and rarely crash.
• Clean up unused rules. Before copying, open the Rules Manager and delete any rules that aren't in use. You'd be surprised how many extra rules accumulate from pasted data or templates.
• Use tables instead of raw conditional formatting. Excel Tables (Ctrl+T) handle formatting more reliably across workbooks. They store formatting per table, not per cell, and Excel knows how to copy them cleanly.

Pro tip: If you copy/paste conditional formatting frequently between workbooks, create a formatting template workbook that you keep open. Copy from that template instead of random workbooks — it keeps the rule table clean and predictable.

One last thing: if you're running Excel 2016 or older, this crash is more common because the rule engine was rewritten in Excel 2019. Upgrade if you can, or stick to paste-as-values for cross-workbook copies.