You can reduce the amount of Excel memory used by:
1. Converting formula to values (Paste Special Values).
2. Reducing the number of formula referencing other worksheets. Formulae which refer to cells or ranges on other worksheets use significantly more memory for dependency trees than formulae which refer to cells or ranges on their own worksheet. The best way to do this is by moving the blocks of cells which are being referenced onto the sheet that refers to them.
3. Checking that the Excel's last cell on each sheet is in the right place: Ctrl-End or Edit-->Goto-->Special-->Last cell. When its not where you want, for instance because of additional formatting or recently deleted cells, you can reset the last cell by deleting all columns to the right and rows below the correct last cell, and then saving the workbook.
4. Storing repetitively used formula in defined Names and using the Names in place of the formula.
5. Using smaller ranges in formula such as INDEX and VLOOKUP.
6. Avoiding references to ranges containing unused or blank cells on other worksheets.
7. Simplifying formatting.
8. Reducing the number of fonts.
9. Switching off Change Tracking for Shared workbooks.
10. Shortening the formula.
11. Move the Pivot Tables to a separate Workbook.
12. Using multi-cell array formula.
13. Removing zero-sized objects.
14. Reducing the number of graphic or embedded objects.
15. Closing open Modules and Forms in the VBE.
