Excel Model Clunkiness - Compare statistics
Hey guys
Thought this could be a relevant topic. I have a pretty decent computer, less than a year old and pretty good specs, 32GB ram. It is such a pain to run through my model, its laggy and takes like 3 minutes to iterate after every save.
the workbook statistics are
Sheets: 15
Cells with Data: 78,621
Formulas: 62,888
Charts: 1
Can anyone take a quick look at their model and compare statistics? Ive deleted all named ranges, have no tables, no cells linked outside of the workbook, no queries, no connections, macros disables and no add ins and its still a pain. Any insight would be appreciated
Try: File-> options -> formulas -> then select 'automatic except for data tables' and 'enable iterative calcs'
Google volatile functions in excel and then have a scan of your sheets to see if you've loaded up on any of them (OFFSET/INDIRECT etc.).
Having unbalanced ranges in some formula like sumproduct/sumif etc. can lead to spreadsheet volatility too, eg. SUMIF(B2:B5,A1,C2:C6) - (the ranges don't match).
Conditional formatting can sometimes be a bastard if you have too much of it.
Be mindful that it only takes one single cell/row/column to immensely slow down an entire workbook, no matter how simple it may be. It can be a manual process but try clearing whole tabs/chunks of formula from cells (do not delete them! just the info inside them) and if things start to speed up (despite your sheet being loaded with ERROR/NAs) well then you've located the potential area that the problem exists.
I know of atleast 5 offset formulas off the top of my head and im sure theres many more, will look into it thank you
Try cleaning all custom legacy formats with a macro - they may be hidden and Excel struggles above 10,000 of them. Replace all vlookups (if applicable) to index match. Then change the file format to .xlsb - should make it more responsive.
How many circulars do you have and what are they?
Interest expense in development models in particular can slow models WAY down.
Can you clarity what all the tabs are for? 15 is really excessive so I’d say it’s likely the model is just built out inefficiently as far as content and formulas. E.g. you don’t need separate tabs for a draw schedule or any financing. They should all be built into the cash flow tab, just one example.
Named ranges can also be detrimental if you have a significant number
Download and install Macabacus and run the Name Scrubber. There are likely a lot more named ranges and broken links that you're not seeing.
Unde quia sint expedita necessitatibus. Fuga et quia recusandae odit omnis. Et eveniet laboriosam enim.
Voluptas doloremque natus consectetur ratione. Dolores qui vel sed.
Ea accusamus beatae omnis eaque. Minima distinctio nulla qui ea.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...