• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to speed up calculation

JAMIR

JAMIR

Member
Hello Sir,
Sir, my file size is 4 MB. And this file contain macro, vlookup, index match fuction, Pivot Table. Now this file is taking to time for calculating the formulas. My work is suffering very badly.

Sir, could you help me that this file formulas would be work very fine. My system confg. core2due & 2 GB RAM. File containing 12000+ rows data.
What the solution? Now i am unable to upload this file due to large file.
Regards,


Zameer Shaikh
 
Zameer

There are a number of things that can be causing the spreadsheet to calculate slowly
Mostly it is due to poor layout and then over use of Volatile Functions and data tables.

Other things to consider are use of external links links to other data sources or other Workbooks.

You may wish to have a look at:
http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

Another thing is to rebuild the dependancy tree, which can become convoluted from time to time. Save your file and backup, then open the file. Ctrl+Alt+Shift+F9
Wait until it recalculates, then save the file again
Close Excel and re-open Excel then the file

I would then recommend reading:
http://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

Have you tried saving the file as a *.xlsb (Binary Excel File) which are generally much smaller
 
Last edited:
Hi Zameer ,

4 MB is beyond the capability of this forum ; however you can use any public file-sharing site such as RapidShare , DropBox , SpeedyShare to upload your file , give others permission to download the file and post the link in this same thread.

Narayan
 
Hui said:
Zameer

There are a number of things that can be causing the spreadsheet to calculate slowly
Mostly it is due to poor layout and then over use of Volatile Functions and data tables.

Other things to consider are use of external links links to other data sources or other Workbooks.

You may wish to have a look at:
http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

Another thing is to rebuild the dependancy tree, which can become convoluted from time to time. Save your file and backup, then open the file. Ctrl+Alt+Shift+F9
Wait until it recalculates, then save the file again
Close Excel and re-open Excel then the file

I would then recommend reading:
http://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

Have you tried saving the file as a *.xlsb (Binary Excel File) which are generally much smaller
Click to expand...

Respected Sir,

There is only one problem i found that, i have 1500+ items and i have 2 diff sheet call receipt & issue and i use the formula sumif in itemmaster register to get the current stock.

In Issue sheet there is 15000+ rows and Receipt Sheet 5000+ rows. The sumif formula is taking time to calculate the enter code number. Then its return the value to related with code number. If i remove the sumif function its work faster.

Is there any trick to get the current stock after +- of particular code number.

Warm Regards,

Zameer
 
You must log in or register to reply here.
Back
Top