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.