Excel crashes copying cells with conditional formatting to another workbook

30-Second Fix: Clear the Clipboard and Try Again

This sounds too easy, but I've seen it work hundreds of times. The clipboard gets corrupted with a large set of conditional formatting rules, and Excel chokes.

1. Close the destination workbook. Don't save changes if it's already open and broken.
2. In the source workbook (the one with the conditional formatting), press Esc once to cancel any pending copy operation.
3. Go to the Home tab, and in the Clipboard group, click the small arrow at the bottom-right corner to open the Clipboard pane.
4. Click Clear All at the top of the pane.
5. Close the Clipboard pane. Now copy only a small range of cells (like 5 rows) and paste into a new blank workbook. If it works, the problem is a corrupted clipboard entry. If it still crashes, move to the next fix.

Expected outcome: After clearing the clipboard, copying and pasting should work without a freeze. If it does, you're done.

5-Minute Fix: Copy Just the Values, Then Reapply Formatting

The real issue is that Excel has to calculate all the conditional formatting rules in the new workbook. If the source workbook uses a lot of INDIRECT or volatile formulas in the conditional formatting, Excel can lock up.

1. In the source workbook, select the range with conditional formatting. Right-click and choose Copy (or Ctrl+C).
2. Switch to the destination workbook. Right-click the top-left cell where you want to paste.
3. Under Paste Options, click the 123 icon (Values only). This pastes just the numbers and text, stripping all formatting.
4. Now copy the conditional formatting rules separately. Go back to the source workbook, select the same range, and press Ctrl+C.
5. In the destination workbook, right-click the same pasted range, go to Paste Special, and choose Formats (the paintbrush icon).
6. If the paste finishes without a crash, you're golden. If it still crashes, the conditional formatting rules themselves are the problem—likely too many rules or conflicting rules.

Expected outcome: You should see the values appear first, then the formatting applied on top. If the formatting paste crashes, the rules are corrupt.

15+ Minute Fix: Clean Up Conditional Formatting Rules and Reset Excel

This is for when the first two fixes fail. I've seen Excel crash because a single workbook has over 200 conditional formatting rules. The fix is to strip them down and then reset Excel's configuration.

Step 1: Check for excessive or conflicting rules

1. In the source workbook, select the entire range that has conditional formatting (press Ctrl+A twice if needed).
2. Go to Home > Conditional Formatting > Manage Rules.
3. In the Conditional Formatting Rules Manager, look at the dropdown at the top. Change it from Current Selection to This Worksheet.
4. Count the rules listed. If there are more than 50 rules in the list, you need to simplify them. Delete any duplicate rules—right-click and choose Delete Rule.
5. Look for rules that use =INDIRECT, =OFFSET, or =RAND(). These volatile functions force Excel to recalculate every time you copy. Change them to direct cell references if possible. For example, replace =INDIRECT("A"&ROW()) with =A1.
6. After cleaning, close the Rules Manager. Save the source workbook.

Step 2: Test copy again

Try copying the cleaned-up range to a new workbook. If it works, you're done. If it still crashes, the problem isn't just the rules—it's Excel's own settings.

Step 3: Reset Excel to default settings

1. Close all Excel workbooks.
2. Press Win + R, type excel /safe, and press Enter. This opens Excel in Safe Mode, which disables add-ins and custom settings.
3. In Safe Mode, open the source workbook (yes, it might take a moment). Try copying the conditional formatting range and pasting into a new workbook.
4. If it works in Safe Mode, the crash is caused by an add-in or a corrupt registry key. Go back to normal Excel and disable add-ins: File > Options > Add-ins, click Go next to Manage COM Add-ins, then uncheck all add-ins.
5. If the crash still happens even in Safe Mode, the registry key for conditional formatting is likely corrupted. Close Excel. Press Win + R, type regedit, and press Enter.
6. Navigate to HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options (change 16.0 to 15.0 for Excel 2013, 14.0 for 2010).
7. Look for a key named ConditionalFormatting or CFRules. Right-click it and select Delete. If you don't see one, don't create it—just skip.
8. Close Registry Editor. Reboot your computer, then open Excel normally. Try copying and pasting the conditional formatting range again.

Expected outcome: After the registry reset, Excel should handle the copy/paste without crashing. If it still doesn't, you may have a corrupt workbook that needs to be rebuilt from scratch.

Pro tip: If you frequently move data between workbooks, avoid using conditional formatting that depends on other sheets. Instead, use a helper column with a formula that returns TRUE/FALSE, then apply conditional formatting to that helper column. It's much more portable and less crash-prone.