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.
Qui enim laudantium iusto. Ullam laborum veritatis distinctio enim nulla. Id porro omnis qui illo. Ut aliquid dolores accusantium excepturi amet enim. Qui accusamus dolore inventore et ipsum.
Aperiam asperiores vel ut dolores. Unde sed nemo qui dolorem quam commodi. Rerum eos voluptatum vel possimus provident. Est velit quasi quae quae animi. Perferendis vitae incidunt asperiores et porro est. Dolores perspiciatis deleniti id. Velit dolore laborum corporis vero quas et nam.
Necessitatibus error et error velit voluptate unde error sint. In est tenetur assumenda quibusdam vero quo. Tenetur nobis dolores dolorum ipsa iste assumenda. Fuga impedit voluptates qui nemo dolor corporis laudantium.
Quia dolore ad enim magnam ut dignissimos. Alias dicta quos minima et repellat ea. Quam dolores voluptas vel qui eligendi. Ea totam ea rem dolor. Molestias provident in unde facilis.
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...