Excel crashes on copy-paste between workbooks with conditional formatting

You're pasting and Excel just dies. I get it.

You've got two workbooks open. Both have conditional formatting — highlights, color scales, icons. You copy a few cells from one, switch to the other, hit Paste, and Excel freezes or closes. Maybe you get a "Microsoft Excel has stopped working" popup. This has happened to me more times than I can count, especially with Excel 2019 and Microsoft 365.

The fix: paste values only, then reapply formatting

Here's what actually works, step by step. Don't skip any of these.

Step 1: Copy the source data normally

Select the cells in your source workbook. Press Ctrl+C or right-click and choose Copy. You'll see the marching ants around your selection. That's fine.

Step 2: Switch to the target workbook and paste values only

Go to the workbook where you want the data. Right-click the top-left cell where you want to paste. Under Paste Options, click the icon that looks like a clipboard with "123" on it — that's Paste Values. You can also use the keyboard shortcut Ctrl+Alt+V, then press V, then Enter.

After this step, you should see the data appear but with none of the original conditional formatting. No colors, no icons. That's exactly what we want for now.

Step 3: Remove any old conditional formatting in the paste area

Select the range you just pasted into. Go to the Home tab, click Conditional Formatting > Clear Rules > Clear Rules from Selected Cells. This wipes out any leftover rules that might conflict with what you'll add next.

You should see a clean, unformatted block of numbers or text.

Step 4: Apply your target workbook's conditional formatting

Now here's the key: don't copy the source formatting. Instead, recreate the rules you need using the target workbook's existing rules or by setting up new ones. If the target already has similar rules on another range, use Format Painter to copy them from there to your new range. Click a cell with the formatting you want, click Format Painter (the paintbrush icon in the Home tab), then highlight your pasted range.

The formatting should now match. And Excel won't crash because you never actually copied the source conditional formatting objects — you only brought over the raw values.

Why this works

The crash happens because conditional formatting rules are stored as objects in Excel's memory. When you copy cells with conditional formatting between two workbooks, Excel tries to merge those objects. If both workbooks have different rule structures — overlapping ranges, conflicting priority levels, or rules that reference other sheets — Excel's memory management can't handle it. It's a known bug that's been around since Excel 2016. Microsoft's official patch notes mention "improved stability when pasting conditional formatting between workbooks," but in my testing, they never fully fixed it.

By pasting values only, you sidestep that object merge entirely. You're just putting in raw data. Then adding formatting from the target workbook keeps everything in one memory space.

Less common variations of this issue

Paste Special > Formats also crashes

Some users try Paste Special > Formats to avoid bringing the data. That still copies the conditional formatting objects and can crash just as hard. Use Format Painter instead — it works within the same workbook's rule set.

Crash only happens with merged cells

If your source workbook uses merged cells with conditional formatting, the crash gets even worse. Unmerge the source cells before copying, or copy only the data and reformat later. Merged cells + conditional formatting = a disaster waiting to happen.

Error message: "Cannot shift objects off sheet"

This one pops up when you paste into a sheet that has too many conditional formatting rules already — Excel's limit is about 65,000 rules per workbook, but performance tanks well before that. If you see this, go to Home > Conditional Formatting > Manage Rules, and delete any rules you don't need.

How to prevent this going forward

I've learned to do two things to avoid this headache entirely.

1. Keep a template workbook. Build your conditional formatting once in a master workbook. When you need to move data between files, paste values into the template. The formatting's already there, so you don't need to copy it.
2. Turn off automatic calculation before pasting. Go to Formulas > Calculation Options > Manual. After pasting and formatting, switch back to Automatic. This stops Excel from trying to recalculate everything mid-paste, which can trigger the crash.
3. Limit conditional formatting to the exact range needed. Don't apply it to entire columns — that creates thousands of unnecessary rules that slow Excel down and increase crash risk.

These steps take about 30 seconds total and save you from losing unsaved work. Trust me, I've been there.