• 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.

Speeding up Excel via hardware

Eloise T

Active Member
It's taking about 4 minutes for one of my spreadsheets to calculate...and it gets progressively worse as the worksheet expands every week.

Anyone out there have a handle on what the best hardware upgrade is to speed up large Excel files? If on a limited budget, is it best to invest in more RAM first? ... or faster CPU with 4 or more cores?
 
Most of Excel speed issues are caused by a combination of poor worksheet layout and poor choice of formula for doing the work

Avoid Volatile functions especially where they are in large arrays of formulas

Excel 2010 generally calculates faster than Excel 2013, which is also faster than 2016. Obviously there is a loss of functionality using older versions.

Using as much RAM as you can afford, also speeds up things.

Close other applications, close all of them except Excel, especially Web Browsers. Web Browsers chew up a lot of RAM.

I haven't tried but it might be worth adding a 32GB Thumb Drive and setting it up for Ready Boost drive. This enables the PC to use the Thumbdrive as a RAM Cache and may also assist. 32GB is as big as Ready Boost can go, so no need for a larger thumb drive.
upload_2018-10-18_13-19-21.png

In the Excel goto File, Options, Advanced and scroll down to Formulas
upload_2018-10-18_13-17-26.png

Have you read:
https://trumpexcel.com/suffering-from-slow-excel-spreadsheets/
or
any of the several pages at: http://www.decisionmodels.com/index.htm
 
Indeed before buying a new machine (or new hardware) it is worth the trouble to invest (a lot of) time in workbook analysis and improve the concept, lay-out, build-up,... how you want to call it.
If it is only the amount of data, perhaps try to divide and conquer. Example (I used that technique very often with success) keep the data external, and use pivots using the external data link. Depending on the formulae required, it can be you don't need any extra: count, sum, % of ... all these are possible with those damned pivots.
You might be using an Excel version that comes with PowerPivot/PowerQuery. Those can make formulae obsolete (like vlookup...).
Going to add those links from @Hui to my catalogue of references. Here is another one that has been shared already frequently over here https://fastexcel.wordpress.com/.
 
Last edited:
Most of Excel speed issues are caused by a combination of poor worksheet layout and poor choice of formula for doing the work

Avoid Volatile functions especially where they are in large arrays of formulas

Excel 2010 generally calculates faster than Excel 2013, which is also faster than 2016. Obviously there is a loss of functionality using older versions.

Using as much RAM as you can afford, also speeds up things.

Close other applications, close all of them except Excel, especially Web Browsers. Web Browsers chew up a lot of RAM.

I haven't tried but it might be worth adding a 32GB Thumb Drive and setting it up for Ready Boost drive. This enables the PC to use the Thumbdrive as a RAM Cache and may also assist. 32GB is as big as Ready Boost can go, so no need for a larger thumb drive.
View attachment 55911

In the Excel goto File, Options, Advanced and scroll down to Formulas
View attachment 55910

Have you read:
https://trumpexcel.com/suffering-from-slow-excel-spreadsheets/
or
any of the several pages at: http://www.decisionmodels.com/index.htm
Have you or anyone you know used the FastExcel3 diagnostic program?
 
I'm having trouble getting FastExcel3 to do anything helpful. It created a "diagnostic" Excel sheet but I have no idea how to use the information to my advantage.

As I understand it, FastExcel3 is supposed to figure out if you have formulas that can be "refined" to make your syrupy Excel file RUN instead of crawl.

I attached the file FastExcel3 created from my monster Excel file.

Can you make any sense of it?
Thanks for anything you can figure out.
 

Attachments

  • FastExcel WorkBook Profile for MASTER INVOICE.xlsx
    26.9 KB · Views: 9
Monster Excel file:confused:? Only 5 sheets, 2 containing formulae, and about 1275 cells used and 9 formulae. That does not seem so excessive to be honest.
About the results (for as far I somehow read the report correctly). Full sheet calculation is only 1.8 milliseconds. Yet 41% of the sheet formulae are volatile and the workbook's full recalculation is above 2 minutes, a whopping 14.8s per formula. But I have seen way worst.
So it seems to suggest you can improve your formulae used. I wonder if this slow calculation is caused by formulae in the external workbooks?
I also notice only one amber alert in the report about the environment and temp files used (114). But that does not tell me anything, I really do not know what it means. Would it be the the macro is opening like 114 external files?
Perhaps you can share the formulae used and the macro code. And see if this wonderful community can help you improve those.
 
Monster Excel file:confused:? Only 5 sheets, 2 containing formulae, and about 1275 cells used and 9 formulae. That does not seem so excessive to be honest.
About the results (for as far I somehow read the report correctly). Full sheet calculation is only 1.8 milliseconds. Yet 41% of the sheet formulae are volatile and the workbook's full recalculation is above 2 minutes, a whopping 14.8s per formula. But I have seen way worst.
So it seems to suggest you can improve your formulae used. I wonder if this slow calculation is caused by formulae in the external workbooks?
I also notice only one amber alert in the report about the environment and temp files used (114). But that does not tell me anything, I really do not know what it means. Would it be the the macro is opening like 114 external files?
Perhaps you can share the formulae used and the macro code. And see if this wonderful community can help you improve those.

{=IF($A5<>"",IFERROR(IF(COUNTA(IF(TRIM($A$5:$A$24999)=TRIM($A5),$A$5:$A$24999))-1<COLUMNS($K5:K5),"","Dup. WO# in Row "&SMALL(IF((TRIM($A$5:$A$24999)=TRIM($A5))*(ROW($A$5:$A$24999)<>ROW()),ROW($A$5:$A$24999)),COLUMNS($K5:K5))),"-"),"--")}

...and yes, it is a [Ctrl] + [Shift] +[Enter] formula which is located in Columns K and L (the last two) and the right side of the picture below.

upload_2018-10-20_14-56-45.png
 
as a start

Why don't you apply a trim to Column A in Say Column Z
eg: =Trim(A2) and copy that down to Z25000
then copy paste as values Column Z back to Column A

Then you can remove the trim functions from the formula
 
Next is

Do you actually need to tell the user which row the duplicate is in?
Teach them how to use Filter and look for the value in Column A

Because if you don't

you can simplify it to:
=IF($A5<>"",IF(COUNTIF($A$5:$A$24999,$A5)>1,"Dup. WO# "&A5,"-"),"--")
no array formula
 
I will have questions for clarification when attempting to implement these changes.

Much to consider and experiment with. Thanks so much for your input.
 
Back
Top