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)

Most Helpful
2mo 
Capital360, what's your opinion? Comment below:

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.

2mo 
22percentirr, what's your opinion? Comment below:

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

  • Analyst 2 in IB - Cov
2mo 

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.

Learn More

300+ 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 more
2mo 
22percentirr, what's your opinion? Comment below:

Oh 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 

2mo 
developermonkey, what's your opinion? Comment below:

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.

2mo 
Tommy-Cruise, what's your opinion? Comment below:

Named ranges can also be detrimental if you have a significant number

2mo 
NowWhat, what's your opinion? Comment below:

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.

2mo 
22percentirr, what's your opinion? Comment below:

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.

Start Discussion

Career Advancement Opportunities

January 2023 Investment Banking

  • Lazard Freres (+ +) 99.5%
  • Jefferies & Company (▽01) 99.1%
  • Lincoln International (▽01) 98.6%
  • Financial Technology Partners (▽01) 98.1%
  • William Blair (▲08) 97.7%

Overall Employee Satisfaction

January 2023 Investment Banking

  • Canaccord Genuity (▲04) 99.5%
  • William Blair (▲04) 99.0%
  • Lincoln International (▲09) 98.6%
  • Jefferies & Company (▲06) 98.1%
  • Financial Technology Partners (▲09) 97.6%

Professional Growth Opportunities

January 2023 Investment Banking

  • Lazard Freres (▲15) 99.5%
  • Financial Technology Partners (▲09) 99.1%
  • Lincoln International (= =) 98.6%
  • Jefferies & Company (▽03) 98.1%
  • William Blair (▲01) 97.7%

Total Avg Compensation

January 2023 Investment Banking

  • Director/MD (6) $592
  • Vice President (24) $418
  • Associates (136) $262
  • 3rd+ Year Analyst (9) $194
  • 2nd Year Analyst (80) $172
  • 1st Year Analyst (257) $171
  • Intern/Summer Associate (42) $166
  • Intern/Summer Analyst (185) $91