Very Large Excel - Some Columns Are Slow to Copy and Paste

[ShawnC84]

All,

I created a very large Excel file (i.e. hundreds of MBs or 100,000 rows by 369 columns) for calculating a sample of about 100,000 insurance policies across five or so different companies. I started off by building out the first row with all the needed formulas and then have copied them down to row 100,000 of the worksheet. I have been the columns in sections of about 50 and as expected it can take the computer up to 10 seconds to complete the copy and paste. On rare occasions the copy and paste can take 30 to 60 minutes to complete and it does seem to be repeatable for the same columns. To be clear this is only the copy and paste. If I turn on the "Automatic" calculations, I usually stay in Manual mode once I build out the full spreadsheet, the entire completed worksheet still calculates relatively quick (i.e. a few seconds). Are there certain formulas that Excel struggles with copying and pasting more than others? I have not been able figure out the reason.

Computer Details:
Windows 10 64bit
Excel 365 64bit
Intel i7-7700K
32 GB Ram

Thanks for any thoughts.

 

[DRSteele]

Welcome to the forum.

Which Excel functions have you included in the formulas? Some volatile functions slow things down considerably. Other culprits include formulas that use arrays, heavy use of conditional formatting, user-defined functions and formulas that require error-handling.

Have you considered turning the data into an official Excel Table? Putting the formula in any cell of a Table will populate the entire field (i.e., the column of the Table) thus speeding calculation considerably.

Can you upload some sample data for us to mess around with? Include the various formulas. Use xl2bb to upload it.

There are dozens of websites with advice on speeding Excel files. Here is one.

 

[ShawnC84]

Hi DRSteele,

Attached you will see snapshots of the columns (OA through QZ in the original spreadsheet)/formulas that are giving me the trouble. As you can see most of the formulas are VLOOKUPs with some INDEX, IF, PRODUCT, SUM, and ROUNDs thrown in for good measure. The formulas used in columns OA through Qz are very similar to the formulas used in earlier columns, which do not have the copy paste time delay. Is it possible that Excel gets "overwhelmed" despite having plenty of memory available? I have not yet tried to make it an official Excel Table, but I will give it a try. Also below is the output from the xl2bb. Please let me know your thoughts. Thanks

100,000 character limit hit (Part 1 or 2)

Excel Copy Paste Example - Most Ros Removed.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB	FC	FD	FE	FF	FG	FH	FI	FJ	FK	FL	FM	FN	FO	FP	FQ	FR	FS	FT	FU	FV	FW	FX	FY	FZ	GA	GB	GC	GD	GE	GF	GG	GH	GI	GJ	GK	GL	GM	GN	GO	GP	GQ	GR	GS	GT	GU	GV	GW	GX	GY	GZ	HA	HB	HC	HD	HE	HF	HG	HH	HI	HJ	HK	HL	HM	HN	HO	HP	HQ	HR	HS	HT	HU	HV	HW	HX	HY	HZ	IA	IB	IC	ID	IE	IF	IG	IH	II	IJ	IK	IL	IM	IN	IO	IP	IQ	IR	IS	IT	IU	IV	IW	IX	IY	IZ	JA	JB	JC	JD	JE	JF	JG	JH	JI	JJ	JK	JL	JM	JN	JO	JP	JQ	JR	JS	JT	JU	JV	JW	JX	JY	JZ	KA	KB	KC	KD	KE	KF	KG	KH	KI	KJ	KK	KL	KM	KN	KO	KP	KQ	KR	KS	KT	KU	KV	KW	KX	KY	KZ	LA	LB	LC	LD	LE	LF	LG	LH	LI	LJ	LK	LL	LM	LN	LO	LP	LQ	LR	LS	LT	LU	LV	LW	LX	LY	LZ	MA	MB	MC	MD	ME	MF	MG	MH	MI	MJ	MK	ML	MM	MN	MO	MP	MQ	MR	MS	MT	MU	MV	MW	MX	MY	MZ	NA	NB	NC	ND	NE	NF	NG	NH	NI	NJ	NK	NL	NM	NN	NO	NP	NQ	NR	NS	NT	NU	NV	NW	NX	NY	NZ	OA	OB	OC	OD	OE	OF	OG	OH	OI	OJ	OK	OL	OM	ON	OO	OP	OQ	OR	OS	OT	OU	OV	OW	OX	OY	OZ	PA	PB	PC	PD	PE	PF	PG	PH	PI	PJ	PK	PL	PM	PN	PO	PP	PQ	PR	PS	PT	PU	PV	PW	PX	PY	PZ	QA	QB	QC	QD	QE	QF	QG	QH	QI	QJ	QK	QL	QM	QN	QO	QP	QQ	QR	QS	QT	QU	QV	QW	QX	QY	QZ
3	Key	GridID	County	County Tier	Zip Code	PO Name	THS Territory	Territory Type	DtC_THS	Dtc_THS_Banded	Cov A Limit	PPC	Type	Occupancy	Cov B %	Cov B Limit (Buyup)	Cov C %	Cov C Limit	Cov D %	Cov D Limit (Buyup)	Age of Home	Age of Home Banded	Insurance Score	Claims	Construction Type	Residence Type	Age of Insured	Age of Insured Banded	Deductible	Ded AOP Percent	W/H Deduct	Roof Material	Roof Shape	Age of Roof	Number of Stories	Sq Feet	Wind_Exclusion	Prior_Liabilty	Full Water	AAL + Reinsurance - All	AAL + Reinsurance - Cov A	AAL + Reinsurance - Cov C	Count	Win	Average - All	Foremost - All	NCRB_2018 - All	NCRB_2011 - All	THS_NC_Normal - All	THS_SC - All	AMS - All	AMPC - All	Auto_Owners - All	ASI - All	Average - A	Foremost - A	NCRB_2018 - A	NCRB_2011 - A	THS_NC_Normal - A	THS_SC - A	AMS - A	AMPC - A	Auto_Owners - A	ASI - A	Average - B	Foremost - B	NCRB_2018 - B	NCRB_2011 - B	THS_NC_Normal - B	THS_SC - B	AMS - B	AMPC - B	Auto_Owners - B	ASI - B	Average - C	Foremost - C	NCRB_2018 - C	NCRB_2011 - C	THS_NC_Normal - C	THS_SC - C	AMS - C	AMPC - C	Auto_Owners - C	ASI - C	Average - D	Foremost - D	NCRB_2018 - D	NCRB_2011 - D	THS_NC_Normal - D	THS_SC - D	AMS - D	AMPC - D	Auto_Owners - D	ASI - D						Territory	Base - Fire	Base - EC	Base	Calendar Year Modifier	Deductible	Ded_Max Credit	Age of Home	Insurance Score	Claims	Construction	Residence Type	Age of Insured	Occupancy	WH_Exclusion	Fireline	Settlement	WH_Ded	Comp Vacant	Cov A	Cov B	Cov C - Fire	Cov C - EC	Cov D	Territory	Base DW	Base OS	Base PP	Age of Insured	Construction Type	Residence Type	Roof_Shape	Roof_Material	Territory Factor	Age of Home	Deductible	Claims	Insurance Score	PPC	DtC	CovA UVRC	CovB UVRC	CovC UVRC	Wildfire Score	Water Scaling	Water Damage Limit	Policy Fee	Cov A	Cov B	Cov C	Cov D	WH Excl	Water	Territory	Territory Type	AOP and WH Ded Same	Key - Fire - A	Key Factor/UVRC - Fire - A	Base Premium - Fire - A	Deductible_Amt (406) - Fire - A	Key - EC - A	Seasonal	Modified Key Premium (Product) - A	Wind Mit - A	WH_Exclusion A	Modified Key Premium (Sum) - A	Key Factor/UVRC - EC - A	Base Premium - EC - A	Deductible_Amt (406) - EC - A	Key - Fire - C	Key Factor/UVRC - Fire - C	Base Premium - Fire - C	Deductible_Amt (406) - Fire - C	Key - EC - C	Seasonal	Modified Key Premium (Product) - C	Wind Mit - C	WH_Exclusion C	Modified Key Premium (Sum) - C	Key Factor/UVRC - EC - C	Base Premium - EC - C	Deductible_Amt (406) - EC - C	WH_Exclu_Credit (A3) - EC - A	Ded - Step 1 - EC - A	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - A	Ded - Step 4 - EC -A	Ded Step 5 - EC - A	WH_Exclu_Credit (A3) - EC - C	Ded - Step 1 - EC - C	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - C	Ded - Step 4 - EC -C	Ded Step 5 - EC - C	Cov A	Cov B	Cov C	Cov D	Unprotected	Territory	Territory Type	AOP and WH Ded Same	Key - Fire - A	Key Factor/UVRC - Fire - A	Base Premium - Fire - A	Deductible_Amt (406) - Fire - A	Key - EC - A	Seasonal	Modified Key Premium (Product) - A	Wind Mit - A	WH_Exclusion A	Modified Key Premium (Sum) - A	Key Factor/UVRC - EC - A	Base Premium - EC - A	Deductible_Amt (406) - EC - A	Key - Fire - C	Key Factor/UVRC - Fire - C	Base Premium - Fire - C	Deductible_Amt (406) - Fire - C	Key - EC - C	Seasonal	Modified Key Premium (Product) - C	Wind Mit - C	WH_Exclusion C	Modified Key Premium (Sum) - C	Key Factor/UVRC - EC - C	Base Premium - EC - C	Deductible_Amt (406) - EC - C	WH_Exclu_Credit (A3) - EC - A	Ded - Step 1 - EC - A	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - A	Ded - Step 4 - EC -A	Ded Step 5 - EC - A	WH_Exclu_Credit (A3) - EC - C	Ded - Step 1 - EC - C	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - C	Ded - Step 4 - EC -C	Ded Step 5 - EC - C	Cov A	Cov B	Cov C	Cov D	Unprotected	NCRB_2008_Territory	ASI_Territory	Territory Type	AOP and WH Ded Same	Key - Fire - A	Deviation - Fire - A	Key Factor/UVRC - Fire - A	Base Premium - Fire - A	Deductible_Amt (406) - Fire - A	Key - EC - A	Deviation - EC - A	Seasonal	Modified Key Premium (Product) - A	Wind Mit - A	WH_Exclusion A	Modified Key Premium (Sum) - A	Key Factor/UVRC - EC - A	Base Premium - EC - A	Deductible_Amt (406) - EC - A	Key - Fire - C	Deviation - Fire - C	Key Factor/UVRC - Fire - C	Base Premium - Fire - C	Deductible_Amt (406) - Fire - C	Key - EC - C	Deviation - EC - C	Seasonal	Modified Key Premium (Product) - C	Wind Mit - C	WH_Exclusion C	Modified Key Premium (Sum) - C	Key Factor/UVRC - EC - C	Base Premium - EC - C	Deductible_Amt (406) - EC - C	WH_Exclu_Credit (A3) - EC - A	Zone_Dev EC - A	Ded - Step 1 - EC - A	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - A	Ded - Step 4 - EC -A	Ded Step 5 - EC - A	WH_Exclu_Credit (A3) - EC - C	Zone_Dev EC - C	Ded - Step 1 - EC - C	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - C	Ded - Step 4 - EC -C	Ded Step 5 - EC - C	Cov A	Cov B	Cov C	Cov D	Unprotected	Territory	Tier	PPC	Construction	Territory	Step C (Product)	Step D (Form)	Step E (Settlement)	Step F (Base Rate)	Cov A Step G (Base Premium)	CovC (Base Premium)	Age of Home - A	Residence Type - A	Occupancy - A	Deductible - A	Age of Home - C	Residence Type - C	Occupancy - C	Deductible - C	Cov A - Step V	Cov C - Step V	Claims	Roof Age	Insurance Score	NonPay	Age of Insured	Advanced Quote - A	Advanced Quote - C	Cov A	Cov B	Cov C	Cov D	Territory NCRB	Territory AMPC	Territory Type	AOP and WH Ded Same	Key - Fire - A	Age of Insured	Age of Home	Claims	Territory	Insurance Score	Key Factor/UVRC - Fire - A	Base Premium - Fire - A	Deductible_Amt (406) - Fire - A	Key - EC - A	Seasonal	Modified Key Premium (Product) - A	Wind Mit - A	WH_Exclusion A	Modified Key Premium (Sum) - A	Age of Insured	Age of Home	Claims	Territory	Insurance Score	Key Factor/UVRC - EC - A	Base Premium - EC - A	Deductible_Amt (406) - EC - A	Key - Fire - C	Age of Insured	Age of Home	Key Factor/UVRC - Fire - C	Base Premium - Fire - C	Deductible_Amt (406) - Fire - C	Key - EC - C	Seasonal	Modified Key Premium (Product) - C	Wind Mit - C	WH_Exclusion C	Modified Key Premium (Sum) - C	Age of Insured	Age of Home	Key Factor/UVRC - EC - C	Base Premium - EC - C	Deductible_Amt (406) - EC - C	WH_Exclu_Credit (A3) - EC - A	Ded - Step 1 - EC - A	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - A	Ded - Step 4 - EC -A	Ded Step 5 - EC - A	WH_Exclu_Credit (A3) - EC - C	Ded - Step 1 - EC - C	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - C	Ded - Step 4 - EC -C	Ded Step 5 - EC - C	Cov A	Cov B	Cov C	Cov D	Unprotected	Territory	Territory Type	AOP and WH Ded Same	Key - Fire - A	Key Factor/UVRC - Fire - A	Base Premium - Fire - A	Deductible_Amt (406) - Fire - A	AOH - Fire - A	AOI - Fire - A	Claims - Fire - A	DtC - Fire - A	Insurance Score - Fire - A	Roof Age - Fire - A	Roof_Material - Fire - A	Year Built - Fire - A	Key - EC - A	Seasonal	Modified Key Premium (Product) - A	Wind Mit - A	WH_Exclusion A	Modified Key Premium (Sum) - A	Key Factor/UVRC - EC - A	Base Premium - EC - A	Deductible_Amt (406) - EC - A	AOH - EC - A	AOI - EC - A	Claims - EC - A	DtC - EC - A	Insurance Score - EC - A	Roof Age - EC - A	Roof_Material - EC - A	Year Built - EC - A	Key - Fire - C	Key Factor/UVRC - Fire - C	Base Premium - Fire - C	Deductible_Amt (406) - Fire - C	AOH - Fire - C	AOI - Fire - C	Claims - Fire - C	DtC - Fire - C	Insurance Score - Fire - C	Roof Age - Fire - C	Roof_Material - Fire - C	Year Built - Fire - C	Key - EC - C	Seasonal	Modified Key Premium (Product) - C	Wind Mit - C	WH_Exclusion C	Modified Key Premium (Sum) - C	Key Factor/UVRC - EC - C	Base Premium - EC - C	Deductible_Amt (406) - EC - C	AOH - EC - C	AOI - EC - C	Claims - EC - C	DtC - EC - C	Insurance Score - EC - C	Roof Age - EC - C	Roof_Material - EC - C	Year Built - EC - C	WH_Exclu_Credit (A3) - EC - A	Ded - Step 1 - EC - A	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - A	Ded - Step 4 - EC -A	Ded Step 5 - EC - A	WH_Exclu_Credit (A3) - EC - C	Ded - Step 1 - EC - C	Ded - Step 2 (Adjusted Ded Credit) - EC - A	Ded - Step 3 - EC - C	Ded - Step 4 - EC -C	Ded Step 5 - EC - C	Cov A	Cov B	Cov C	Cov D	Unprotected
4	36.4391014535134, -79.7800959685164	0364307978	Rockingham	Remainder of State	27288	Eden	310	Inland	200	200	250000	1	DP-3	Seasonal	10%	0	0%	0	10%	0	22	20	450	0	Masonry	1 Family	41	40	500	0.2%	1000	Tile	Gable	12	1	1894	No	>$300,000	Yes				1	1	1047		867	721	433.5	2012	867	1151	1184	1142	954.741		804	572	301	1986.93	804	1083	1184	903	0		0	0	0	0	0	0	0	0	75.125		63	149	19	0	63	68	0	239	0		0	0	0	0	0	0	0	0	250000	5000	75000	175000	75000	A	0	0	0	1	0.06	0	0	0	0	-0.15	0	0	0	0	0	10	0	0	10	0	0	0	0	310	684.19	4.25	5	0.92	0.85	1	1	0.83	1.18	1	1.033	0.92	2.11	1	1	1.8909	0	0	1	0	0	25	1986.93	0	0	0	0	0	310	Inland	No	24	10.4	250	0	29	1.55	44.95	0	0	44.95	12.79	575	-21.275	7	0	0	0	1	1.55	1.55	0	0	1.55	42.42	66	-2.706	0	0	0	0.037	21.275	-21.275	0	0	0	0.041	2.706	-2.706	804	0	63	0	0	60	Inland	No	22	10.4	229	-11.45	20	1.65	33	0	0	33	12.79	422	-67.52	10	0	0	0	2	2.15	4.3	0	0	4.3	42.42	182	-32.76	0	0	0	0.16	67.52	-67.52	0	0	0	0.18	32.76	-32.76	572	0	149	0	0	60	86	Inland	No	22	1.6629	10.4	380	-19	20	1.52835	1.65	50.4356	0	0	50.4356	12.79	645	-103.2	10	1.7043	0	0	0	2	1.5985	2.15	6.87355	0	0	6.87355	42.42	292	-52.56	0	1.329	0	0	0.16	103.2	-103.2	0	1.39	0	0	0.18	52.56	-52.56	903	0	239	0	0	64	Premier	0.945	0.903	0.98	0.836	0.836	0.836	564.02	1319	0	80	0	-66	-69	0	0	0	0	1264	0	1	0.931	1.125	0.944	1	-66	0	1184	0	0	0	310	310	Inland	No	24	0.88	1.27	1	0.94	1	10.4	262	0	29	1.55	44.95	0	0	44.95	0.96	1.13	0.85	1.61	1	12.79	853	-31.561	7	0.88	1.27	0	0	0	1	1.55	1.55	0	0	1.55	0.96	1.13	42.42	71	-2.911	0	0	0	0.037	31.561	-31.561	0	0	0	0.041	2.911	-2.911	1083	0	68	0	0	310	Inland	No	24	10.4	250	0	0.7692	1	0.7132	1	0.7296	1	0.8	1	29	1.55	44.95	0	0	44.95	12.79	575	-21.275	1	1	0.7132	0.9355	0.7296	1	0.9	0.91	7	0	0	0	0.7692	1	0.7132	1	0.7296	1	0.8	1	1	1.55	1.55	0	0	1.55	42.42	66	-2.706	0.7692	1	0.7132	0.9355	0.7296	1	0.9	0.91	0	0	0	0.037	21.275	-21.275	0	0	0	0.041	2.706	-2.706	301	0	19	0	0
Calculator

Cell Formulas
Range	Formula
A4	A4	=VLOOKUP(RANDBETWEEN(1,MAX(Shared!$W$3:$W$98967)),Shared!$W$3:$X$98967,2,TRUE)
B4	B4	=CONCATENATE("0",LEFT(A4,2),MID(A4,4,2),"0",MID(A4,FIND("-",A4,1)+1,2),MID(A4,FIND(".",A4,5)+1,2))
C4	C4	=IF(VLOOKUP($A4,Territory_Assignment_Shared,1,TRUE)=$A4,VLOOKUP($A4,Territory_Assignment_Shared,3,TRUE),NA())
D4	D4	=IF(VLOOKUP($A4,Territory_Assignment_Shared,1,TRUE)=$A4,VLOOKUP($A4,Territory_Assignment_Shared,16,TRUE),NA())
E4	E4	=IF(VLOOKUP($A4,Territory_Assignment_Shared,1,TRUE)=$A4,VLOOKUP($A4,Territory_Assignment_Shared,2,TRUE),NA())
F4	F4	=IF(VLOOKUP($A4,Territory_Assignment_Shared,1,TRUE)=$A4,VLOOKUP(A4,Territory_Assignment_Shared,4,TRUE),NA())
G4	G4	=$DT4
H4,OB4	H4	=VLOOKUP($EW4,Territory_Type_THS_Proposed,2,FALSE)
I4	I4	=IF(VLOOKUP($A4,Territory_Assignment_Shared,1,TRUE)=$A4,VLOOKUP(A4,Territory_Assignment_Shared,15,TRUE),NA())
J4	J4	=IF(I4<=1,MROUND(I4,0.25),IF(I4<=10,MROUND(I4,1),IF(I4<=30,MROUND(I4,5),IF(I4<=100,MROUND(I4,10),MROUND(I4,25)))))
K4	K4	=IF($M4="DP-1",$CQ4,IF(CQ4>=75000,CQ4,IF(CR4>=75000,CR4,IF(CS4>=75000,CS4,IF(CT4>=75000,CT4,IF(CU4>=75000,CU4,75000))))))
L4	L4	=VLOOKUP($A4,Territory_Assignment_Shared,5,FALSE)
M4	M4	=VLOOKUP(RANDBETWEEN(1,2),Weight_Type,2,FALSE)
N4	N4	=IF($M4="DP-1",VLOOKUP(RANDBETWEEN(1,10),Weight_DP_1_Occupancy,2,FALSE),VLOOKUP(RANDBETWEEN(1,10),Weight_DP_3_Occupancy,2,FALSE))
O4	O4	=ROUND(P4/K4,2)+VLOOKUP($M4,Included_CovB_NCRB2018,2,FALSE)
P4	P4	=VLOOKUP(VLOOKUP(RANDBETWEEN(1,10),Weight_CovB_Percent,2,FALSE)*K4,Weight_Possible_CovB_Limits,1,TRUE)
Q4	Q4	=ROUND(R4/K4,2)
R4	R4	=VLOOKUP(VLOOKUP(RANDBETWEEN(1,10),Weight_CovC_Percent,2,FALSE)*K4,Weight_Possible_CovC_Limits,1,TRUE)
S4	S4	=ROUND(T4/K4,2)+VLOOKUP($M4,Included_CovD_NCRB2018,2,FALSE)
T4	T4	=IF($N4="Vacant",0,VLOOKUP(VLOOKUP(RANDBETWEEN(1,10),Weights_CovDPercent,2,FALSE)*K4,Weights_Possible_CovD_Limits,1,TRUE))
U4	U4	=MIN(100,IF($M4="DP-3",0,RANDBETWEEN(0,50))+VLOOKUP(RANDBETWEEN(1,INDEX(Weight_Age_of_Home,103,MATCH($E4,Weight_Age_of_Home_HeaderRow,0))),OFFSET(Weight_Age_of_Home,0,MATCH($E4,Weight_Age_of_Home_HeaderRow,0)-1),Weight_Age_of_Home_ColumnCount-MATCH($E4,Weight_Age_of_Home_HeaderRow,0)+1,TRUE))
V4	V4	=VLOOKUP($U4,Age_Banding,1,TRUE)
W4	W4	=VLOOKUP(RANDBETWEEN(1,ISWeights!$A$16),ISWeights!$A$2:$B$15,2,TRUE)
Y4	Y4	=VLOOKUP(RANDBETWEEN(1,10),Weight_Construction_Type,2,FALSE)
Z4	Z4	=VLOOKUP(RANDBETWEEN(1,100),Weight_Residence_Type,2,FALSE)
AA4	AA4	=RANDBETWEEN(25,80)
AB4	AB4	=VLOOKUP(AA4,Age_Banding,1,TRUE)
AC4	AC4	=VLOOKUP(RANDBETWEEN(1,10),Weight_Deductible,2,FALSE)
AD4	AD4	=ROUND(AC4/K4,3)
AE4	AE4	=IF($AK4="Yes",AC4,IF(AC4=500,1000,AC4))
AF4	AF4	=VLOOKUP(RANDBETWEEN(1,10),Weight_Roof_Material,2,FALSE)
AG4	AG4	=VLOOKUP(RANDBETWEEN(1,10),Weight_Roof_Shape,2,FALSE)
AH4	AH4	=IF($U4<=20,$U4,12)
AI4	AI4	=VLOOKUP(RANDBETWEEN(1,100),Weights_Number_of_Stories,2,FALSE)
AJ4	AJ4	=MAX(200,ROUND($K4/$AJ$2,0))
AK4	AK4	=IF($AK$

 

[rlv01]

How long does it take if you turn off workbook calculation before doing the copy and paste ? (don't forget to turn it on again after)

 

[ShawnC84]

Hi rlv01,

Usually it only takes Excel a second or so to move from Automatic calculation to Manual calculation. Though today and yesterday after the final column copy and paste it only took Excel 5ish seconds to go from Manual to Automatic (including the formula calculation). Moving back to Manual afterward took about 15 minutes. This was a new development. I have never had that problem before.

Thanks

 

[rlv01]

I've never had an issue switching to manual calculation. My guess is that something else was going on and the switch to manual was coincidental. With 100k rows of cells containing vlookup formulas, you should always turn off calculation for copy/paste. Or before sorting.

 

[ShawnC84]

All,

I have determined the cause of the slow copy and paste. It is the "+5" in the below formula:
=INDEX(Year_Built_Table_THS_Proposed,MATCH($C4,Year_Built_RowHeaders_THS_Proposed,0),MATCH($G$1-$U4,Year_Built_ColHeaders_THS_Proposed,1)+5)

No idea why that would be the issue but removing the "+5" solves the problem.