!Out of Memory

Memory problems are typically experienced on 32-bit Excel installations — that is the default used by Microsoft’s installer even on 64-bit Windows systems. Regardless of how much memory you have in your computer, a limited amount of memory is available from Excel for the application, all workbooks open, and all the add-ins running. The usual cause of an out of memory error is a combination of a big workbook (or multiple workbooks open) combined with a big query.

Things you can do:

  • in an ideal world, install the 64-bit version of Excel (your license permits either version).
  • place large query results on their own worksheet, starting at cell A1; do not stack them side-by-side on a single worksheet.
  • reduce the size of the workbook — delete any unneeded worksheets, remove conditional formatting from any large areas, split multiple large sheets into separate workbooks.
  • reduce the size of the query — drop any unneeded columns, limit the number of rows.
  • move the query to its own (empty) workbook (no formatting!).
  • close all other workbooks.
  • temporarily disable any other add-ins.