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
Comments (13)
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.
-
- Learn more
- | Suggested Resource
Learn More300+ video lessons across 6 modeling courses taught by elite practitioners at the top investment banks and private equity funds -- Excel Modeling -- Financial Statement Modeling -- M&A Modeling -- LBO Modeling -- DCF and Valuation Modeling -- ALL INCLUDED + 2 Huge Bonuses.
Learn moreOh god mate, basically the whole model from overview, drivers, schedule and massing are all just one clusterfuck of circulars. would prolly have to re build the whole model. Ill remove all the v lookups from the newwer guys tho, good idea thank you
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.
Aliquam beatae rerum quae voluptatum dolores numquam. Ex magni enim quod dolor. Doloribus modi assumenda cupiditate voluptatem ratione minus veritatis qui. Iusto sit sit aut omnis. Nisi praesentium commodi enim minus. Sed qui repellendus est et optio aut nihil.
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...