Why does excel recalcute when copy/pasting values?

[dooder]

I have a mildly complex workbook with a lot of index(match) functions to other workbooks, and one particularly processor intensive index(max) array function, so I understand why it sometimes takes a minute or so to process changes on my older laptop (excel 2016). But occasionally I run ad hoc reports by filtering a sheet and copy/paste values into a temp worksheet. Rarely more than a 100 rows of 10 or so cols. Frequently, this action causes the whole workbook to update, locking the workbook up for minutes at a time. Any idea what could be causing this, or a workaround to avoid it?

 

[jasonb75]

Pasting data is an action that triggers recalculation. Any formulas affected by the cells that you are pasting to will be recalculated, as will any formulas that use volatile functions, INDIRECT, OFFSET, TODAY(), NOW(), are the most common ones, there are others. Conditional formatting is also considered volatile.

Due to formula hierarchy, a formula that doesn't contain a volatile function will still be recalculated if it refers to one that does, sometimes you can trace a very long chain of formulas before finding the source of a problem.

When you say that you're copying approx 100 rows by 10 cols, are you copying just the used range, or are you selecting entire rows / columns including the empty space beyond the data? Doing that causes file bloat when you paste and is known to be problematic. I've seen files go from ~300kb to ~7mb by when people do this, with things that should be instant suddenly taking several seconds.

There are other things that could be contributing to the problem, large arrays, lookup / match functions that refer to large ranges or full columns will be notably slow.
If there is nothing that you can change in the formulas, setting calculation to manual might help. I don't think that having the source workbooks open makes the formulas calculate any faster, but it is something that you could test.

 

[dooder]

Thanks. I'm in the process of trimming my index/match functions to no reference entire cols, and that helps a bit. But I'm still stumped. I just selected and copied no more than 20 rows of data in a single col, and paste/value into an entirely different workbook, and again, recalc the whole book.