Why is my Excel soooo slow...

[dfalir]

Guys hi, i need someone to help me with the following question please...

I have built an excel that has 9 worksheets. Out of these, 4 are coming directly from a mysql database. The other 6 worksheets, make some calculations, which are.

19 Vlookup functions (on different worksheets, on this excel only(
8 mid, left, right formulas in order to extract names, etc
4 Sumproduct formulas.

Every change or adition I make, my I7 @ 2,93 GHz seem unable to respond. By that i mean that if I make any change to any cell, the result will be displayed after 8-10 seconds. Why is taking so much time? What can i do to improve timings? My excel data, is not that much. The actual data from all worksheets must sum up to less 1000rows.

and one other question. if i have made a simple calculation. like price that includes vat, in one worksheet, and then i need to display the same value in another worksheet, is it faster to use vlookup to retrieve the value or is faster to do this simple calculation all over again?

Any other word of advice of how can i make this excel faster? I want this excel to work fast on an atom netbook, so I am thinking that if it takes 8 secs for any change on an i7 intel, how many ages will it take on netbook laptop?

Please help...

 

[RoryA]

Also, this is pointless:
=IFERROR(IF(VLOOKUP((B24&D24);Analytics!D:M;1;FALSE)=B24&D24;"Product Found";"Error");"CHECK SPELLING!!!")
since you are specifying an exact match, it will either return that or an error, nothing else. Simpler to use:
=IF(ISNA(MATCH((B24&D24);Analytics!D:D;0));"CHECK SPELLING!!!";"Product Found")

 

[dfalir]

Guys, thank you very much for all your replies. I had all my formulas with sumifs, and then i read on the article i posted you that sumproduct is somewhat quicker. I changed all sumifs to suproducts...grrr!

Which is faster after all?

lol!

 

[dfalir]

also thank you all for all your suggestions. I will modify my excel according to what you say.

Rorya, thank you also for the correction, just saw it.

 

[RoryA]

SUMIFS is faster than SUMPRODUCT.

 

[dfalir]

Guys also one more thing.

Lets say i have simple calculations in one sheet. For Example, Price (net) * VAT = Final Price.

Now i want this to show also in another worksheet. Is it better if i vlookup the price from the first worksheet, or redo the calculation on the new cell, instead? which is faster?

 

[RoryA]

If you are returning multiple columns of data for a lookup value, use MATCH in one column to get the row you need, then use several INDEX formulas to return the values from each column. Much more efficient than using multiple VLOOKUPs and looking up the same value each time.