1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'The Lounge' started by Eloise T, Oct 18, 2018.

  1. Eloise T

    Eloise T Active Member

    Messages:
    811
    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?
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,632
    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
    muna and GraH - Guido like this.
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    811
    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: Oct 18, 2018
  4. Eloise T

    Eloise T Active Member

    Messages:
    811
    Thanks to both of you, Hui and GraH.
  5. Eloise T

    Eloise T Active Member

    Messages:
    811
    Have you or anyone you know used the FastExcel3 diagnostic program?
    ThrottleWorks likes this.
  6. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,632
    Last edited: Oct 19, 2018
  7. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    811
    No I haven't either. Are you having trouble running it, @Eloise T?
  8. Eloise T

    Eloise T Active Member

    Messages:
    811
    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.

    Attached Files:

  9. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    811
    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.
    ThrottleWorks likes this.
  10. Eloise T

    Eloise T Active Member

    Messages:
    811
    {=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
  11. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,632
    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
    ThrottleWorks and GraH - Guido like this.
  12. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,632
    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
    ThrottleWorks and GraH - Guido like this.
  13. Eloise T

    Eloise T Active Member

    Messages:
    811
    I will have questions for clarification when attempting to implement these changes.

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

Share This Page