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!
3 Answers
You can temporarily disable recalculations. Go to Excel Options, then Formulas, and under Calculation Options set Workbook Calculation to Manual.
To manually recalculate press F9
answered Jul 31, 2013 at 8:34
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.
answered Jul 31, 2013 at 8:33
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.
? ActiveSheet.Shapes.Countand see the number.. and you will have to delete them.. or add new sheet.. and copy paste formats/formulas/values.. not copy paste everything.