Forum Discussion
Computational intense Excel file (no code), need to run in cloud - Options?
I ran into the 5MB file limit. I have the latest 4.2 GHz iMac with 32 GB of ram and a SSD. Excel chokes.
Breaking this up is not an option. Is there no cloud option for this scenario?
Yes, hardware is quite good. Not sure if more powerful hardware will help, i'd try to optimize the file first.
You may try to convert it in binary format (save with .xlsb extension), that shall reduce the file size and improve the perfrmance somehow.
Don't know Mac version, on PC Pro version there is the option Clean Excess Cell Formatting (it affects only cells without values), sometimes it reduces the size and improve performance significantly, especially for files what exist with lot of changes for years.
Currently, the spreadsheet is 58,667 rows x 14,101 columns. In my head, that is not really that large but I guess it is a few numbers for Excel to manipulate. I need roughly the same amount of columns as rows but it feels I am pushing Excel to it's workable limit for the moment. Total bummer.
- With that many columns, you definitely take an effeciency hit.
I bet you are already using manual calculation, but with that many cells updating, I wonder if using VBA to hold off screen updating till calculations are done would be worth it. It may even be better to do all calculations in the VBA space.
If you could get doen to using a Columnar set-up might be fastest.... Eric,
That's a lot. Excel limit is about 16K columns, you are close to it https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
That's not correct, but converting to usual dozen of columns you have in equivalent about 80K millions of rows - fully out of my experience.
Cores usage - that's still under review, see https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/9472407-will-excel-for-mac-2016-utilize-multiple-cores-on
On PC multi-thread is available, but i guess not for calculation - for sorting and like. However, just in case please check you settings
As for the binary format - all my experience says it's in times less than XML (.xlsx) and less than old binary .xls
I'm lost. Olaf Hubel, may someone from Microsoft comment this?
Yeah, multiple cores for calcs are out on the Mac. Very sad. If it was not Microsoft, I wouldn't even believe it. Yet, here we are getting punished for owning a Mac.
Microsoft should offer a cloud solution for spreadsheets like this. I would love to be able to manipulate the sheet without having to wait minutes or hours after performing a change. Currently, I am increasing the number of columns available to the sheet to XFC, one less than allowed. It will take 12-18 hours for this process to complete. Will most likely have a 1GB spreadsheet after this.
Four years ago this was out of the question. I guess I will have to wait another 5 years of tech advancement for a better experience.
Thanks SergeiBaklan for all of your advice and help. I will try the file saves in the other format one more time but each time I have done this I get a larger file size and no observable difference in performance.
New find. Excel for Mac will only use one processor to calculate. No option for multi-core support on the Mac. Microsoft really sucks. Especialy considering I have a machine with 8 cores ready to be used if needed. Tested on a Windows machine and it is no faster minus the calculation part. Waiting 60 seconds for a result doesn't bother me. Waiting 12 - 18 hours for formulas to copy over does bother me.
I saved to xlsb format and it increased the file size (xls) and 617 MB (xlsb). Yikes!
I had to increase the cells making calcs and now the spreadsheet is at ~480 MB. Copy cell formulas to the new cells took over 12 hours of operation by the computer.
I will try your suggestions! The math is easy, there are a lot of cells doing math that depend on the previous cell.
I am solving a double integral in Excel and when my delta x goes from 0.01 to 0.001 things get harry.
