Spreadsheet with lots of formulas and named ranges takes time to load and clocks each time a cell is updated

[NSRL]

I have a spreadsheet that is used to manage the annual budget along with monthly forecast, monthly resource costs, vacations etc. in separate sheets in the same workbook. So needless to say there are lots of formulas in these sheets and references across sheets. I am also using named ranges and structured tables.

I am assuming this has led to a spreadsheet that takes time to load when opening and every time you edit a cell, the sheet clocks for a long time. Is there a way to optimise without resorting to using "Manual" calculation instead of "Automatic"?

 

[Jeffrey Mahoney]

Do you have dynamic named ranges?
Do you have Structured Excel Tables with lots of formulas?
Do you have lots of Array Formulas?

 

[jkpieterse]

Have you recently checked for named ranges that are duplicated and/or not in use? Try my Name Manager for better Named Ranges insights: Free Name Manager Excel add-in

 

[jkpieterse]

Posted elsewhere too: Spreadsheet with lots of formulas and named ranges takes time to load and clocks each time a cell is updated
Please mention this next time.

 

[NSRL]

Do you have dynamic named ranges?
Do you have Structured Excel Tables with lots of formulas?
Do you have lots of Array Formulas?

Structured tables have limited formulas, just the basic like totals. However, there are lots of formulas based on the structured tables.
No array formulas
Using ranges based on ranges

 

[Jeffrey Mahoney]

How big is the workbook? Very large sets of data? You may have something corrupt in your workbook. On one WB I had to stop calculations one sheet at a time until I found the culprit.

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

There is no need to repeat the link provided by jkpietrse, but if you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[NSRL]

How big is the workbook? Very large sets of data? You may have something corrupt in your workbook. On one WB I had to stop calculations one sheet at a time until I found the culprit.

Thanks I will try that out.

 

[NSRL]

Posted elsewhere too: Spreadsheet with lots of formulas and named ranges takes time to load and clocks each time a cell is updated
Please mention this next time.

Sorry, I meant to do that.

 

[NSRL]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.
Be sure to follow & read the link at the end of the rule too!

There is no need to repeat the link provided by jkpietrse, but if you have posted the question at other places, please provide links to those as well.
If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

I will do that next time. Thank you.