I am working on an Excel 2007 workbook (less than 1gb in size). There is one sheet which has formulae linked to other two sheets in the same workbook. The problem is that it becomes really slow whenever I make any change (or update the data in the other two sheets). I am using Intel i5 Windows 7 OS with 8gb RAM (which I think should be more than enough for this kind of work). Please help!
The less than 1 GB in size is worrisome, as if you're nearing a 1 GB excel file. Excel is notoriously slow with large files. If you have a lot of formulas updating due to links, it will bog down.
What you can do is go to
file (button) - options and go to the
formulas section and change the
calculation options to manual. Now it will only update when you press F9 so you can control when it gets slow.
You can temporarily disable recalculations. Go to
Excel Options, then
Formulas, and under
Calculation Options set
Workbook Calculation to
To manually recalculate press F9
A large number of cells usually isn't a problem. It's more likely that the spreadsheet contains some volatile cells, which in turn have many dependents. Every time you edit ANY cell, ALL volatile cells get recalculated (unless the calculation mode is set to manual - but that can be risky).
You could run the profiler in the FormulaDesk add-in, and set it to profile volatile cells. It will show you all the volatile cells in your file as well as their dependents (which will also be volatile), and how long they take to execute.
Also, you can check whether your spreadsheet is bloated with orphaned blank cells, which increases the size of the file. Look for the 'Trim Bloated Workbook' button. It can also find all unused custom styles, which can also be a problem.
Disclaimer: FormulaDesk is my product.