How do you manage volatile functions, array formulas in workbook with large data set

[danhendo888]

So I have about 20 sheets in one workbook where each sheet has 10 columns of formulas using index, row, column, match, frequency, if, vlookup. They are all pointing to a sheet in the same workbook with 20,000+ rows of data.

Each day I have to delete the 20,000+ rows of data.
Paste a new set of 20,000+ rows of data.

I wasn't encountering any problems for the past week, but now Excel is having a tough time of pasting the data.
I always see the Calculating (4 processors): 0% in the status bar.

I could split the sheets into separate spreadsheets but before I do, I want to hear from you guys whether you have had similar experiences and if there are any solutions to my pickle.

Thanks guys

 

[danhendo888]

At the moment, I have made formulas to update manually. I will see how that goes.

 

[Yongle]

Is your file size INCREASING inadvertently despite the fact that you are removing 20k rows and inserting 20k rows each day ?
- try selecting all rows below the last row with any data
- delete those rows, save, close & reopen the file
- or create a new workbook (from scratch) with the latest data and see if the same behaviour befalls that after a few days
- if size-creep is the issue, work out what is causing that to happen (come back here if you need further help)


Consider using manual calculation mode - see this link - https://www.techwalla.com/articles/how-to-switch-to-manual-calculation-in-excel

Edit

At the moment, I have made formulas to update manually. I will see how that goes.

ah - I now see that you have already considered this

 

[danhendo888]

Is your file size INCREASING inadvertently despite the fact that you are removing 20k rows and inserting 20k rows each day ?
- try selecting all rows below the last row with any data
- delete those rows, save, close & reopen the file
- or create a new workbook (from scratch) with the latest data and see if the same behaviour befalls that after a few days
- if size-creep is the issue, work out what is causing that to happen (come back here if you need further help)

Hi Yongle,
My workbook has been hovering at 13MB file size for the past three days.

With 1000 rows of data, it is around 2MB.
After I pasted 20,000 rows of data, it goes up to 13MB after I save it.
The data just by itself on a new workbook is 12MB.

In addition to keeping my formulas manually refreshed, I found that SHIFT + F9 refreshes only the active sheet in a workbook.
That seems to make things much better.
When I hit just F9, it took a long time to calculate. I suspect the problem (with refreshing the whole workbook at once) is just the fact that I have approx. 200 columns with volatile functions over ~20 sheets in one workbook.
SHIFT + F9 should be good enough for now

 

[Fazza]

How I manage it is by using a different approach. This was addressed in a separate recent thread. Basically working like a database - without volatile functions & especially without array formulas. The performance difference between sub-optimal & optimal setups - even if staying with formulas - can be ENORMOUS. For anyone to give helpful advice, I'll repeat my earlier comments from 23-Jul-19

If you want a specific/helpful answer, suggest you start a new thread and explain the setup, give some sample input data, matching results, etc, etc. And the Excel version. IMO the more work you put into asking the question the better the answer/s you'll get - so it is worth the effort. With a good explanation of the setup, there is a good chance someone will offer a really good solution.