Excel file freezes when using Filter / Vlookup / Random

[looper]

So, I have a colleague that keeps telling me her Excel has issues and she can't use it and so on. We use Office 365 with auto updates.

The most severe case is the attached file, every command results in instant freeze and you have to wait from 1 to 5 min.

I'm not an expert, I don't know much about Excel, so I was wondering if someone could take a look at this file and check what's wrong with it. She copied some data from other excel files into this one, if this has any importance.

3.49 MB file on MEGA

mega.nz

 

[Tivakaran]

Hi. NEVER leave formula open unless its variable calculation. The reason why your excel 'moves slow' is it freaking so much formulas which I think is unnecessary..

If you dont need the formula, just copy paste as values. this will help.

 

[looper]

Thanks for your input, I'll be sure to explain this to my colleague.

 

[Alex Blakenburg]

Try turning the data into a table (ctrl+T) and change the formulas to the ones I have in the columns with the red heading.
• The vlookup formulas are doing a full column lookup ie 1M+ rows
(converting to a table will make it easier to address just the rows in use, currently around 20k)
• replacing the vlookup with Index Match should help too.

You need to update your account profile to show what version of excel you are using. If you have MS365 you could use Xlookup instead.

eri ALL Index.xlsx
	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
1	Material_No	Part_Desc	Last_Yr_Util	MOQ	Act_Code	Retail_Price	Dealer_Price	Dist_Price	Pret vanzare	Cost	Subtitude_Part	Qty_Assembly	UOM_Base_Price	UOM_Sale_Price	Qty_For_UOM	Price_Cat_Desc	Pret la coduri modif o data vlk	Pret la coduri modif o data vlk2	Pret 1	Pret la coduri modif de doua ori vlk	Pret la coduri modif de doua ori vlk3	Pret 2	Pret coduri modif de trei ori vlk	Pret coduri modif de trei ori vlk4	Pret euro 3	Pret coduri modif de patru ori vlk	Pret coduri modif de patru ori	Pret euro 4	Preturi comune	Preturi comune5	Pret euro 5	Promotii	Promotii6	Pret euro FINAL
2	0123-149	WASHER FLAT	23		1	2.06	1.34	0.92	2.06	0.92		0.000	PC	PC	1.000	A - Captive Parts	#N/A	0	2.06	#N/A	0	2.06	#N/A	0	2.06	#N/A	0	2.06
3	0176757	SWITCH & SLEEVE AY	0		1	167.40	100.50	69.00	167.4	69		0.000	PC	PC	1.000	A - Captive Parts	#N/A	0	167.4	#N/A	0	167.4	#N/A	0	167.4	#N/A	0	167.4
4	0460540	ELEC STARTER ASSY	0		USE	00000000000	00000000000	00000000000	0	0	420888995	0.000			0.000	B - Comp Parts	305.57	305.57	305.57	305.57	305.57	305.57	305.57	305.57	305.57	305.57	305.57	305.57
New_Table

Cell Formulas
Range	Formula
I2:I4	I2	=VALUE(F2)
J2:J4	J2	=VALUE(H2)
S2:S4	S2	=MAX(I2,R2)
T2:T4	T2	=INDEX([Pret 1],MATCH([@[Subtitude_Part]],[Material_No],0),0)
V2:V4,AB2:AB4,Y2:Y4	V2	=MAX(S2,U2)
W2:W4	W2	=INDEX([Pret 2],MATCH([@[Subtitude_Part]],[Material_No],0),0)
Z2:Z4	Z2	=INDEX([Pret euro 3],MATCH([@[Subtitude_Part]],[Material_No],0),0)
Q2:Q4	Q2	=INDEX([Pret vanzare],MATCH([@[Subtitude_Part]],[Material_No],0),0)