Takes Forever to Copy Simple Cell Formulas

[gambils]

I have asked this question before- why it seems Excel seems to lock up when I am doing my monthly financial statements doing very simple copy and pastes of simple formulas. The basic reply I got was that the file was likely just very large causing it to lag. But, this month, I pulled out the two main tabs in which I do my copying and pasting and reduced the file size to only 500 KB so size should not be an issue.

Below is a copy/paste to illustrate what I'm talking about. The actual amount cell on the left may be a simple sum of 3-4 cells above it. When I try and copy that very simple sum formula to the Actual YTD column it can take minutes to copy. I don't bother most of the time now- just enter the formula in the cell instead of wasting time waiting for it to copy.

Could it be the worksheet has gotten corrupted somehow- I have one other tab where I link the results of this detail tab to the appropriate cells on the financial statement?

I have tried opening and repairing the worksheet but that has not helped.

Ideas?

<colgroup><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:8996;width:185pt" width="246"> <col style="mso-width-source:userset;mso-width-alt:2962; width:61pt" span="2" width="81"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> </colgroup><tbody>
[TD="class: xl62794, width: 77"]Center[/TD]
[TD="class: xl62794, width: 57"]Account[/TD]
[TD="class: xl62794, width: 246"]Account Descr[/TD]
[TD="class: xl62793, width: 81"]Actual Amt[/TD]
[TD="class: xl62793, width: 81"][/TD]
[TD="class: xl62793, width: 102"]Actual YTD Amt[/TD]

[TD="class: xl62794, align: right"]3046750003[/TD]
[TD="class: xl62794, align: right"]44925[/TD]
[TD="class: xl62794"]PATENT & ROYALTY INC [/TD]
[TD="class: xl62793, align: right"]0.00 [/TD]
[TD="class: xl62793"][/TD]
[TD="class: xl62793, align: right"]727.83 [/TD]

</tbody>

 

[oldbrewer]

turn off autocalculate, repeat the test, report back to us

 

[gambils]

Copying does work immediately with auto-calc turned off.

 

[Fishboy]

I have one other tab where I link the results of this detail tab to the appropriate cells on the financial statement?

Copying does work immediately with auto-calc turned off.

I suspect if you have lots of cells which are referencing other cells then every time you are moving or updating chunks of data the whole workbook recalculates. As you have discovered from oldbrewer's query, turning off auto-calculation allows this to be done instantly.

You may be better off looking into a VBA solution for the copying of your formulas as you could tailor your code to turn off auto-calc, copy the desired formulas then re-enable auto-calc all on the press of a button.

 

[gambils]

VBA is beyond my humble skills at present. So, for the time being, I'm leaving auto-calc off on this particular worksheet- not sure why it has more "chunks of data" than some of my others but it must- and then after I hit copy, I hit save at which point it manually re-calculates. I've never done a manual re-calculation before so don't even know how but, as I said, save is fast and it seems to work. So I do have a workaround although curiosity will continue to compel me to figure out the why behind this. Have some other worksheets that also give me fits with taking too much time so I may try this "fix" with them, too. Thanks!

 

[Fishboy]

VBA is beyond my humble skills at present. So, for the time being, I'm leaving auto-calc off on this particular worksheet- not sure why it has more "chunks of data" than some of my others but it must- and then after I hit copy, I hit save at which point it manually re-calculates. I've never done a manual re-calculation before so don't even know how but, as I said, save is fast and it seems to work. So I do have a workaround although curiosity will continue to compel me to figure out the why behind this. Have some other worksheets that also give me fits with taking too much time so I may try this "fix" with them, too. Thanks!

If you want to push a manual calculation through on demand you can either just press F8 or from the formula tab at the top of the screen you can click either the Calculate Now or Calculate Sheet buttons.

It's worth pointing out that turning calculation to manual is at application level not workbook level, meaning if you turn it off for one workbook, it is turned off for all workbooks until you turn it back on.

 

[oldbrewer]

I once published a monthly financial report based on figures that had NOT been auto calculated because I FORGOT - so beware !

 

[SpillerBD]

The types and number of formulas also makes a difference. Most Array formulas can be useful, but also require a lot of processing time to calculate. In those cases there may be alternatives to provide faster more reliable results.

Use the Show Formulas option or CTRL+ ~ to toggle between formulas and results to examine the formulas in your workbook.

 

[gambils]

Ok, I thought I had a fix but now when I've gone to the main tab where I display my results (for instance on a revenue line, I will click +rawdetail A1 to pull the number over from the raw detail tab), even with the auto-calc turned off it is taking forever when I hit "copy" before the border starts rotating showing it is ready to paste.

 

[gambils]

To lend more evidence to my theory that there could be a corruption of the file, I had saved the worksheet and when I just re-opened it two of my columns that had been formatted as general (or possibly text- don't remember which) had been changed to date. Doesn't always happen with this file but it does periodically where I have to re-format columns.