Excel - Slow Copy and Paste - Large Spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1431811%22%20slang%3D%22en-US%22%3EExcel%20-%20Slow%20Copy%20and%20Paste%20-%20Large%20Spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1431811%22%20slang%3D%22en-US%22%3E%3CP%3EAll%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20about%201%2C400%20columns%20(with%20formulas).%20I%20want%20to%20copy%20and%20paste%20these%20formulas%20down%20to%20about%20row%20100%2C000.%20When%20doing%20this%20this%20I%20occasionally%20run%20into%20a%20very%20slow%20paste%20function%20(e.g.%2030%20minutes%20for%20a%20single%20column).%20Strangely%20this%20is%20not%20the%20same%20result%20for%20every%20column.%20Once%20copied%20the%20(I%20have%20the%20workbook%20set%20to%20manual%20calculation)%20the%20formulas%20calculated%20very%20quickly.%20It%20appears%20to%20be%20a%20software%20limitation%20rather%20than%20a%20hardware%20limitation.%20Anyone%20have%20thoughts%20on%20how%20to%20speed%20the%20process%20along%3F%20Here%20is%20an%20example%20of%20a%20fast%20and%20slow%20copy%2Fpaste%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20(About%20%3CSTRONG%3Ea%20Second%3C%2FSTRONG%3E%20to%20Copy%20and%20Paste)%3A%3C%2FP%3E%3CP%3E%3DINDEX(Deductible_Table_THS_Proposed%2CMATCH((%24AN4%2F%24T4)%2CDeductible_Row_THS_Proposed%2C1)%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20(About%20%3CSTRONG%3E30%20Minutes%3C%2FSTRONG%3E%20to%20Copy%20and%20Paste)%3A%3C%2FP%3E%3CP%3E%3DINDEX(Deductible_Table_THS_Proposed%2CMATCH((%24AN4%2F%24T4)%2CDeductible_Row_THS_Proposed%2C1)%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%2B1%3C%2FSTRONG%3E%3C%2FFONT%3E%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECharacteristics%20of%20Note%3A%3C%2FP%3E%3CP%3EMicrosoft%20Office%20365%2064-bit%3C%2FP%3E%3CP%3EI7-7700K%3C%2FP%3E%3CP%3E32GB%20Ram%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1431811%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Visitor

All,

 

I have a spreadsheet with about 1,400 columns (with formulas). I want to copy and paste these formulas down to about row 100,000. When doing this this I occasionally run into a very slow paste function (e.g. 30 minutes for a single column). Strangely this is not the same result for every column. Once copied the (I have the workbook set to manual calculation) the formulas calculated very quickly. It appears to be a software limitation rather than a hardware limitation. Anyone have thoughts on how to speed the process along? Here is an example of a fast and slow copy/paste:

 

Formula (About a Second to Copy and Paste):

=INDEX(Deductible_Table_THS_Proposed,MATCH(($AN4/$T4),Deductible_Row_THS_Proposed,1),1)

 

Formula (About 30 Minutes to Copy and Paste):

=INDEX(Deductible_Table_THS_Proposed,MATCH(($AN4/$T4),Deductible_Row_THS_Proposed,1)+1,1)

 

Characteristics of Note:

Microsoft Office 365 64-bit

I7-7700K

32GB Ram

0 Replies