Recalc one cell vs. entire spreadsheet

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
When I change a cell and press enter, should Excel (97) be recalculating the entire spreadsheet or just that cell + any it impacts? I've noticed on several different files, when I make a change, the status bar shows recalc in progress and counts off % completed. This takes 3-4 seconds and happens even if the cell has no precedents or dependents. I know, get a faster machine. And I don't want to have to toggle automatic recalc. I didn't notice this happening until a week ago. Thoughts or suggestions?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
According to the help file:
"Whenever possible,Excel updates only those cells dependent on other cells that contain values that have changed...The calculation process may take more time if the workbook contains a large number of formulas, if the worksheets contain data tables, or if the worksheets contain functions that automatically recalculate every time the workbook is recalculated.

_________________
Give a man a fish and he'll eat for a day.
Teach that man to fish and he'll complain about having to work for what he once got free.
This message was edited by Steve Hartman on 2002-03-06 11:55
This message was edited by Steve Hartman on 2002-03-06 11:56
 
Upvote 0
Thanks Steve, but what I really wanted to know is what formulas or functions would recalc even if the cells that feed them or vice-versa were not involved. The help file is ambiguous (at least to me) in that respect. My file does contain a large number of formulas including some array formulas. Do they automatically recalculate anytime any cell is changed, even if the changed cell has nothing to do with them? Assume A1 is blank and is not referred to in any formula in the entire workbook. If I enter 1 in A1, the entire workbook appears to recalculate, taking several seconds. I'm looking for a workaround to this situation.
This message was edited by pilot on 2002-03-06 13:04
 
Upvote 0
On 2002-03-06 13:02, pilot wrote:
Thanks Steve, but what I really wanted to know is what formulas or functions would recalc even if the cells that feed them or vice-versa were not involved. The help file is ambiguous (at least to me) in that respect. My file does contain a large number of formulas including some array formulas. Do they automatically recalculate anytime any cell is changed, even if the changed cell has nothing to do with them? Assume A1 is blank and is not referred to in any formula in the entire workbook. If I enter 1 in A1, the entire workbook appears to recalculate, taking several seconds. I'm looking for a workaround to this situation.
This message was edited by pilot on 2002-03-06 13:04

For a better understanding of "Volatility" see http://www.decisionmodels.com/calcsecretsi.htm
 
Upvote 0
Hi Pilot

Too many volatile functions eg, Now(), TODAY() to name only two of many, can have a re-calculation effect. Array formulas are also notorious for this. If you have a lot of Volatile functions condsider placing them into one group of cells and just reference the needed cell. This prevents using functions like TOday() potentionally hundreds of times. If you have a few array formulae consider using "Database functions" and/or Pivot Tables in their place.

You might also want to check the "Maximum iterations" you have under Tools>Options>Calculation it is normally 100
 
Upvote 0
Thanks guys. This was what I needed to hear. I do have lots of INDIRECT functions.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top