Fix Excel "There isn't enough memory to complete this action" Error with Large Files

The Scenario

You open a large Excel file — maybe a sales report with 200,000 rows, a pivot table aggregating multiple sheets, or a workbook with heavy formulas across dozens of columns. Then Excel freezes and throws:

There isn't enough memory to complete this action. Try using less data or closing other applications.

Sometimes it happens mid-edit. Sometimes just on open. The file might be 50MB, or even under 10MB but stuffed with volatile formulas. Either way, Excel has run out of working memory.

Why This Happens

Excel loads the entire workbook into RAM every time it recalculates. The 32-bit version — still bundled with some Office installs — is hard-capped at 2GB RAM, no matter how much your machine has. The 64-bit version can use more, but even that buckles under:

• Files with hundreds of thousands of rows and many formula columns
• Volatile functions like INDIRECT, OFFSET, NOW(), RAND() — these recalculate on every single keystroke
• Large pivot tables with many calculated fields
• Conditional formatting rules spread across entire columns
• Embedded images, charts, or OLE objects inflating file size

Quick Fixes (Do These First)

1. Check Your Excel Bitness

Go to File → Account → About Excel. If you see 32-bit, that's your problem. Uninstall Office and reinstall it — selecting the 64-bit option this time.

Not sure which you have? Check via PowerShell:

# If the path contains "Program Files (x86)", it's 32-bit
Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Office\*\Excel\InstallRoot" | Select-Object PSPath

2. Free Up RAM and Expand Virtual Memory

Close Chrome, Slack, and any other heavy apps before reopening the file. Then bump up Windows virtual memory:

• Press Win + R, type sysdm.cpl, hit Enter
• Go to Advanced → Performance → Settings → Advanced → Virtual memory → Change
• Uncheck Automatically manage, set Initial size to 4096 and Maximum to 8192 (higher if you have the disk space)
• Click Set → OK → Restart

3. Switch to Manual Calculation

Set Excel to manual calculation before opening the file. This stops it from recalculating 50,000 formulas the moment you load it:

// File → Options → Formulas → Calculation options → Manual
// Then open your file

Or paste this into the VBA Immediate Window (Ctrl+G):

Application.Calculation = xlCalculationManual

Hit F9 when you actually need a recalculation.

Permanent Fixes

4. Hunt Down Volatile Formulas

INDIRECT is the worst offender — it forces a full workbook recalculation on every change. Replace it with direct cell references wherever you can.

Press Ctrl+F, search for =INDIRECT, and swap each one for a fixed reference. To find all volatile functions across every sheet at once, run this macro:

Sub FindVolatileFunctions()
    Dim ws As Worksheet
    Dim cell As Range
    Dim volatiles As Variant
    Dim v As Variant
    
    volatiles = Array("INDIRECT", "OFFSET", "NOW", "TODAY", "RAND", "RANDBETWEEN")
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If cell.HasFormula Then
                For Each v In volatiles
                    If InStr(cell.Formula, v) > 0 Then
                        Debug.Print ws.Name & "!" & cell.Address & ": " & cell.Formula
                    End If
                Next v
            End If
        Next cell
    Next ws
End Sub

5. Scope Down Conditional Formatting

Rules applied to entire columns like $A:$A are a silent memory killer. Excel evaluates them across over a million rows, even when your data only goes to row 500.

First, audit how bad it is:

Sub CleanConditionalFormatting()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name & ": " & ws.Cells.FormatConditions.Count & " rules"
    Next ws
End Sub

Then go to Home → Conditional Formatting → Manage Rules and tighten any rule that references a full column. Change $A:$A to something like $A$2:$A$50000.

6. Convert Static Formulas to Values

A formula that never changes is just wasting calculation cycles. Freeze it:

• Select the range
• Ctrl+C to copy
• Ctrl+Shift+V → Paste Special → Values

To do the whole active sheet at once:

Sub ConvertFormulasToValues()
    Dim ws As Worksheet
    Set ws = ActiveSheet
    ws.UsedRange.Value = ws.UsedRange.Value
End Sub

7. Split Raw Data from Analysis

Keeping 200,000 rows of raw data and your pivot tables in the same workbook is asking for trouble. Separate them:

• Store raw data in a dedicated data.xlsx
• Pull it into your analysis workbook via Power Query (Data → Get Data → From File)
• Power Query loads lazily and handles large datasets far more efficiently than direct sheet references

8. Delete Ghost Rows and Columns

Old Excel files accumulate hidden cruft — empty rows Excel still tracks, deleted data that left formatting behind, columns that extend to row 1,048,576 for no reason. This macro finds and removes them:

Sub ReduceFileSize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim lastCol As Long
    
    For Each ws In ThisWorkbook.Worksheets
        lastRow = ws.Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lastCol = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        
        If lastRow < ws.Rows.Count Then
            ws.Rows(lastRow + 1 & ":" & ws.Rows.Count).Delete
        End If
        
        If lastCol < ws.Columns.Count Then
            ws.Columns(lastCol + 1 & ":" & ws.Columns.Count).Delete
        End If
    Next ws
    
    ' Saving as xlsx strips out legacy compatibility overhead
    ThisWorkbook.SaveAs ThisWorkbook.FullName, xlOpenXMLWorkbook
End Sub

Verification

Close Excel fully, then reopen the file. Work through this checklist:

• The file loads without throwing the memory error
• File size check: right-click → Properties. A well-optimized 200k-row file should come in under 20MB
• Open Task Manager (Ctrl+Shift+Esc) while the file is open — Excel's memory usage should stay below 500MB for typical large workbooks
• Force a full recalculation with Ctrl+Alt+F9. No error = you're done
• Check File → Options → Formulas — calculation time should be seconds, not minutes

When Excel Just Can't Handle It

Some datasets are genuinely too large for a spreadsheet. At that point, the right move is to stop fighting Excel and use a tool built for the job:

• Python + pandas: handles millions of rows without breaking a sweat
• DuckDB: run SQL directly on CSV or Parquet files — blazing fast for analytics
• Power BI: purpose-built for large dataset visualization

# Load only the columns you need — cuts memory use dramatically
import pandas as pd

df = pd.read_excel('large_file.xlsx', 
                   usecols=['Date', 'Sales', 'Region'],
                   engine='openpyxl')

result = df.groupby('Region')['Sales'].sum()
print(result)