My brother works with massive Excel spreadsheets. Gigabytes in size and often with a million rows and many sheets within the workbook. He’s already refined the sheets to take advantage of Excel’s multi-threading, but he’s hit a stumbling block. He uses extensive VBA code to aid clarity, but the VB engine is single-threaded, and these relatively simple functions can be called millions of times. Some functions are trivial (e.g. conversion functions) and just for clarity, some could be unwound but that would make the spreadsheets much more complex, others could not be unwound.
If he is bound by slow hard drive access, try using a solid state drive.
If he is bound by limited RAM or frequent disk paging, increase the RAM size.
If he is using a spreadsheet in an inappropriate manner, i.e., as a database, he might want to rethink the design of his project, but that might be a huge undertaking.
A faster computer, more-core CPU, more RAM, all will be helpful.
I had a similar problem, dealing with massive amounts of data 500,000 rows 142 colums - and lots of calculations from all the cols of data. I gave up with Excel and switched to MatLab which is ideal for that sort of thing. You may consider that route. Sorry its not ‘how to speed up excel’ answer, excel is a great tool, just not the right tool for everything.
He’s already far exceeded the design capacity of Excel. I’ve heard good things about MatLab, but if he’s already proficient with VBA, it’s only a very short step to retooling in Access. mySQL would be the better option for the data, but even the built in mdb support would improve the speed by orders of magnitude.
Not sure if this will help, but I’ve found that having too many formula cells at once will bring Excel to its knees. What I’ve found helpful if you’re building it one column at a time from right to left say is to compute a column of cells, leave the formula in the first cell, then copy and paste the remainder of the column as values. If you have dozens of columns and tens of thousands of rows it might save the day.
SAP Crystal Reports is the best reporting tool that I use. I’ve written over a hundred reports with it. Very useful with databases like Oracle and MySQL. We have a Oracle database at work.
It sounds like the OP’s brother has good Tech skills.
I’d still use a contractor to fast track the database. But do some of the work yourself. Use the contractor as a resource to ask questions and learn. Make it clear from the start that you want to be involved in doing the work.
Setting up the user accounts and privileges for MySQL can be tricky. You’ll probably want to setup ODBC for connectivity. Especially if staff in other offices need to report from the database. ODBC is an add on for MySQL that has to be installed and configured… A contractor can get that basic DBA housekeeping stuff done quickly.
If he’s gotten this far, he probably already knows this, but you might remind him to make sure he has no unnecessary Select or Activate statements in his VBA code. They can really bog things down.
Try changing calculation to Manual. But you have to know exactly what you are doing because if Auto Calc is off, you might end up using an incorrect result to make a decision in VBA.
I am with the consensus here that millions of rows suggest a switch to a database. Microsoft Access is quite good for single user databases.
I’m not sure Access can handle millions of rows and gigabytes of data. That is huge.
I’m having trouble imagining an application with that much data.
I’m not sure if Access would be able to handle it. Access has a similar spreadsheet/table much like Excel. It uses VB for calculations. I’d be concerned the performance would be just as bad.
But, I’ve never dealt with that much data before. A 100,000 rows is about the biggest I’ve used. That seemed pretty big too me.
I have heard MySQL can handle big databases. It’s the open source solution for people that aren’t rich enough to buy Oracle.
I’m really not sure that a database is the way to go. He’s modelling oil reservoirs, so he has an Excel template into which he imports new data (for new or updated reservoirs) regularly. There are very complex mathematical functions to be applied, for which Excel is proving great, yet the bottleneck appears to be the simple stuff! I don’t know enough about Matlab to comment on that.
Having only just barely encountered the fast end of Excel slowing down, is one of you able to explain, slowly, using small words, why some of these other programs tend to work faster? I’m always looking for more things to learn, and maybe I’ll put one of these on my list.
It’s more a matter of having the right tool for the job than anything else. Imagine it this way; your brother is using a plow attachment to a riding mower (Excel) to try and plow 100 acres. That setup he’s using might work for a big backyard garden, or even a very small farm, but i’ts not going to work too well on fields above a certain size.
Going to a database or math analysis package is the equivalent of getting a real tractor and plow to work the 100 acres, instead of trying to trick out and soup up the riding lawnmower, which is what you’re asking how to do.
