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"?

 

[NSRL]

Thanks I will try that out.

I missed your question about the size. The spreadsheet is 260KB. The two largest sheets have 50/60 rows and around 30 columns each. This will increase with time as we are just starting a new workbook for the upcoming year.

 

[NSRL]

I just figured what the issue was and thought I should post it here. I was referencing certain ranges for using the INDEX function and instead of specifying an exact area I used something like $B:$O. Today I read somewhere that using a range like this will force excel to loop through all the rows in that range even though majority of it is blank. I changed that to a specific section, something like $B$1:$O$70. The impact was perceptible with the performance improving significantly.