Why does excel recalcute when copy/pasting values?

dooder

New Member
Joined
Jun 27, 2017
Messages
11
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?
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,634
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 27, 2017
Messages
11
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,295
Messages
5,577,237
Members
412,777
Latest member
MrGray
Top