How to fix Not enough memory issue?

In this article we will discuss a common issue we face sometimes, which is not enough memory errors.

Excel cannot complete this task with available resources. Choose less data or close other applications.

Out of Memory

Not enough System Resources to Display Completely

 

There isn't enough memory to complete this action. Try using less data or closing other applications. To increase memory availability, consider:

  • Using a 64-bit version of Microsoft Excel
  • Adding memory to your device

There can be numerous reasons for such issues, and we will try to understand how we can resolve them when we are facing such issues:

Let us check if any of the Excel sheets have some extra formatting that is not required. Sometimes when we format data, we format the entire sheet, and the size of the file increases unnecessarily.

 

Possible reasons

Unnecessary formulas:

Sometimes we have files with formulas copied over to the entire sheet, and we can remove the extra part to make it work.

Contents of the spreadsheet:

The first thing to determine is if the error is specific to one workbook (or others created from the same template). Typical actions that cause memory error messages are:

    • Inserting a row or column 
    • Sorting
    • Performing calculations 
    • Copy and paste
    • Opening or closing the workbook 
    • Running VBA

Extra Add-ins:

sometimes we use a lot of add-ins which we installed but never used which could cause some trouble.

Hidden tabs/Formatting:

Sometimes we have a lot of hidden tabs which are not required, and we can remove them to make it work.

Hit and trial

The last process we can do to find out which sheet is taking up all the space is by hit and trial:

Start deleting the sheets one by one. Please create a copy of the workbook before doing that. After every deletion, check the file size. If it decreases drastically by any single sheet, we can recreate that sheet from scratch.

Convert the sheet to binary:

We can save the file as a binary file in the .xlsb format, and this works sometimes.