Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel formulas are slow to update
If I enter new information onto a worksheet, the item is not included in the
totals box unless I leave the worksheet and then come back to it. I've tried resetting the Tools - Options - Calculations to automatic - but it doesn't help |
#2
|
|||
|
|||
sounds like you have a lot of items being recalculated each time.
Depending on what exactly is being recalculated, there are various things you can do. If this sheet is mostly data and most of the calculations are on other sheets. you can make calculation be manual and set up a macro to calculate just this sheet when anything is changed on this sheet. You might break up the workbook into several smaller workbooks so that a smaller section is being recalculated each time. I have known people that the simplest thing to speed up calcs was to add memory to the computer. There also might be areas where you needed calculations to generate something, but the answer is there now so that the calcs do not need to be done again. A lot of files work much better with some juditious copy and paste special values. "Jools" wrote: If I enter new information onto a worksheet, the item is not included in the totals box unless I leave the worksheet and then come back to it. I've tried resetting the Tools - Options - Calculations to automatic - but it doesn't help |
#3
|
|||
|
|||
Jools wrote:
If I enter new information onto a worksheet, the item is not included in the totals box unless I leave the worksheet and then come back to it. I've tried resetting the Tools - Options - Calculations to automatic - but it doesn't help You are probably experiencing long re-calculation times. A couple of speed-up suggestions [1] Try to replace the formulas using OFFSET and INDIRECT with ones that do not invoke these and other volatile functions. [2] Try to restrict the array formulas, inclusing the formulas with SumProduct to relevant subranges or replace them with formulas which invoke SumIf (or CountIf). [3] Try to replace lookup formulas with the match-type = 0 (or FALSE) with lookup formulas with the match-type = 1. See http://www.decisionmodels.com for more tips. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Don't update excel links | Excel Worksheet Functions | |||
Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
auto update an open excel sheet over network, with mutiple users | Excel Worksheet Functions | |||
How do I get rid of the Excel update? | Excel Discussion (Misc queries) |