Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Need Some good Tips to build a big excel file so it'll RUN Faster.

  1. #1
    Board Regular
    Join Date
    May 2013
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need Some good Tips to build a big excel file so it'll RUN Faster.

    Hello you excel expert ,
    I knew excel but just recently understood it's power, now I'm Planning to build a Gigantic File on excel, probably gonna be reach in pictures, links, macros, and includes 1000's of worksheets ,
    Recently I've done my first macro's and I've notice that macro's enable file actually opens slower, than non-macro's enable file..
    that just the first que to make me nervouse,
    THIS PROJECT IS VERY IMPORTANT TO ME,
    so, I need all your Tips to make this file - really fast - for opening, viewing, and navigating.
    I'll do whatever require from me (including top computer, and etc) but I'll love to get those really insight Tips in the building of the file (like not using Today() function because the cumputer allways trys to redo it, I know that in word I could have resize all the photos - though I don't know how to do it in excel, and as much as you've got)
    Thanks ahead .
    netanel.

  2. #2
    Board Regular
    Join Date
    Apr 2013
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Some good Tips to build a big excel file so it'll RUN Faster.


  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    138
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Some good Tips to build a big excel file so it'll RUN Faster.

    those were just awesome, thanks bro.

  4. #4
    Board Regular
    Join Date
    Apr 2013
    Posts
    86
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Some good Tips to build a big excel file so it'll RUN Faster.


    You're welcome

    Regards.

  5. #5
    Board Regular
    Join Date
    Apr 2013
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Some good Tips to build a big excel file so it'll RUN Faster.

    note the following if you want a fast excel (I believe most people here don't care about it)
    and it will make your life of updating and managing the spreadsheet much easier and faster

    1. Don't Repeat Yourself
    - when designing the excel, don't have any same formula segments repeated within a cell, or across cells
    e.g. avoid =IF(ISNA(VLOOKUP()), 0, VLOOKUP()), this will double the times of lookup required
    A1: = VLOOKUP(), with the other cell = IF(ISNA(A1), 0, A1) to reduce half of the lookups
    e.g. when formula of three cells contains the same formula segment, take it out and find a cell to store that, then for the 3 cells, reference to that cell.
    - when designing the vba, again, don't use the same codes over and over again

    2. Avoid volatile formulas
    INDIRECT, OFFSET, ROWS, COLUMNS, TODAY, RAND()
    cells containing the above functions are volatile and will be re-calculated every time when anything changes, even though the dependents of those cells are unchanged. This reduces the speed of calculation

    3. do smart, avoid duplicated search
    if you have something like vlookup($A1, $B$1:$C$20, 2, 0) then another cell vlookup($A1, $B$1:$D$20, 3, 0)
    you should use a new cell A2 to store =MATCH($A$1, $B$1:$B$20, 0) and then for others, INDEX($C$1:$C$20, $A$2), INDEX($D$1:$D$20, $A$2)
    this again reduce the number of lookups required
    and there are multiple benefits of using INDEX+MATCH to replace VLOOKUP

    4. if your data are well organised, you can even sort your column $B$1:$B$20 ascending
    then instead of using exact match, you can use A2: =MATCH($A$1, $B$1:$B$20, 1) to locate the row
    note: if you are afraid there's no such value in the array, you can make a check =IF(INDEX($B$1:$B$20, $A$2)=$A$1, "correct", "incorrect")
    using type 1 match is much faster than type 0 match

    5. Finally, remember to put same type of data in one single table, it will shorten the formulas and formulas are simpler, faster and easier to be managed

    these pop up from my mind at this moment...there's a lot in fact (which hardly any people know)
    and the fact is, if your workbook design is good enough, using auto calc mode / manual calc mode doesn't differ much in calculation time

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need Some good Tips to build a big excel file so it'll RUN Faster.

    Unfortunately, I search in the 75 excel speeding up tips
    only 2 people result matched for "Volatile", from Tayyab Hussain, and wintermute

    yes, Tayyab Hussain's comment is good, and somehow as a recap of what I mentioned above
    wintermute's is just too short..

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com