Fix Excel 'Not Enough Memory' Error with Heavy Conditional Formatting

EP
Erropedia Team
📅 September 1, 2026 📅 Updated Sep 12, 2026 👁 19 views Software – Microsoft Office Beginner
Excel hits this memory error when conditional formatting rules pile up. Here's how to find and fix the culprits, from biggest to smallest.

Too Many Conditional Formatting Rules Per Cell

Last month I had a client whose 12,000-row inventory sheet wouldn't open without that dreaded memory dialog. We dug in and found almost every cell had seven or eight conditional formatting rules stacked on it. That's the number one cause of this error, and it's almost always from copy-pasting formatted cells over and over.

Each rule is a separate object Excel has to evaluate every time the sheet recalculates or repaints. Fifty cells with one rule each? Fine. Five hundred cells with eight rules each? You're asking for a memory meltdown. The fix is brutal but simple: consolidate the rules.

  1. Select the range that's misbehaving.
  2. Go to Home > Conditional Formatting > Manage Rules.
  3. Change the “Show formatting rules for” dropdown to This Worksheet.
  4. Scan for rules that apply to the same cells. You'll see things like =A1>100 and =A2>100 that should be one rule covering the whole range.
  5. Select the duplicate rules and hit Delete Rule.
  6. Rewrite the surviving rule to cover the full range (e.g., =$A$1:$A$12000).

If you have rules that are basically the same but with different thresholds, merge them using AND or OR logic. For example, instead of two rules for >100 and >200, use one formula: =A1>100 for the first and a separate but broader range rule for the second. You'll often cut the rule count by half or more.

Don't forget to check for rules on entire rows or columns. I've seen people apply a rule to column A:A and then paste it down, creating hundreds of identical rules. That's a performance killer.

Conditional Formatting Applied to Entire Rows or Columns

The second most common culprit is applying a rule to a whole row or column when you only need it for the used range. A client had a rule that highlighted overdue invoices, but it was set to apply to $A:$XFD — that's over 16,000 columns. Excel tries to evaluate that rule for every single cell, even the empty ones. Guess what happens? Memory error.

Here's how to spot it:

  1. Open Manage Rules again.
  2. Look for “Applies to” entries like $A:$A or $1:$1048576.
  3. Click the rule and then the range selector next to the “Applies to” box.
  4. Shrink it to the actual data range. If your data is rows 1–5000, use $A$1:$Z$5000. Don't be lazy and use entire columns.

Also, be careful when inserting rows or columns. Excel sometimes extends the “Applies to” range automatically, and before you know it, you've got a rule spanning the whole sheet. Check every few weeks if you're adding data regularly.

Stale or Invisible Conditional Formatting Rules

The third cause is sneaky: rules that exist but aren't doing anything visible. These come from pasting formatting from other workbooks, or from using table styles that carry hidden rules. They pile up in the background and eat memory without you seeing any highlight on screen.

To find them:

  1. Open Manage Rules and switch the dropdown to This Worksheet.
  2. Look for rules that reference cells outside your data range (like $XFD1 or $A$1000000).
  3. Also look for rules with formulas that return #REF! errors — they're dead weight.
  4. Delete any rule that doesn't actually change the appearance of your data. If you're not sure, temporarily disable it (uncheck the box) and see if anything looks different.

Sometimes you'll find rules that were created by an old Excel version or by a macro that ran amok. I had a client whose accounting workbook had 400+ rules, but only 12 actually did anything. We nuked the rest and the file opened in seconds.

One more trick: if you can't identify the rule manually, use the Go To Special feature. Press F5, click Special, then choose Conditional formats. That selects all cells with any rule, letting you see exactly where rules exist. Then you can clean up the stragglers.

Quick Reference Summary

CauseSymptomFix
Too many rules per cellError appears after copy-pasteMerge duplicate rules, reduce count
Rules on entire rows/columnsError on open, slow scrollingShrink “Applies to” to used range
Stale/invisible rulesError persists after cleaningDelete dead or #REF! rules

Start with the first fix, test your file, then move down the list. Most of the time you'll solve it at step one. If not, you've got the other two to work through. And if you're still stuck after all this, try saving as .xlsb instead of .xlsx — it's a binary format that loads faster and uses less memory. But that's a band-aid, not a cure. Fix the rules first.

Related Errors in Software – Microsoft Office
There is not enough memory or disk space to save the document Word Save Error: Not Enough Memory or Disk Space — Real Fixes Fix 'Excel cannot open the file' error for .xlsx files Outlook Search Dead After Windows Update? Rebuild the Index Recover Unsaved Word Document: Step-by-Step Guide
Excel not enough memory error conditional formatting performance too many conditional formatting rules Excel memory error large file fix Excel memory issue reduce conditional formatting Excel file slow open conditional formatting cleanup

Was this solution helpful?

EP
Erropedia Team
Tech Support Editors
The Erropedia editorial team researches and documents real-world tech errors from across Windows, Linux, macOS, networking, databases, cloud platforms, and more. Every solution is reviewed for accuracy and updated as software and systems evolve.