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

[netanel99]

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.

 

[christchaaya]

I don't know if it's this what you are looking for , but give it a try :

Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros

75 Excel Speeding up Tips - How to speed-up & optimize slow Excel workbooks? | Chandoo.org - Learn Microsoft Excel Online

Regards.

 

[netanel99]

those were just awesome, thanks bro.

 

[christchaaya]

---

You're welcome

Regards.

 

[aaalviny]

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

 

[aaalviny]

I don't know if it's this what you are looking for , but give it a try :

Optimize Slow VBA Code. Speed Up Efficient VBA Code/Macros

75 Excel Speeding up Tips - How to speed-up & optimize slow Excel workbooks? | Chandoo.org - Learn Microsoft Excel Online

Regards.

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