excel overload

I have been using Excel off and on for 25+ years and I have encountered the same
problem each time. It takes too long to calculate my huge spreadsheets. Often up to 30 minutes
and sometimes it stops or I stop it in frustration. In addition I need to do several runs an hour.
I've concluded the software is not suitable for such large spreadsheets described below.
Records often in excess of 100,000 (rows) several columns of formulae.
Can anyone offer an alternative to Excel ?  I possess a Lic Stata V10 software and wondered if
this could be suitable.
I'm using a Dell 7500 Workstation with 24GB hard drive and 320GB free space on my hard drive.
Any help with software advice much appreciated
Thanks
Ian
Ian BellretiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MlandaTCommented:
I think the title of your question perhaps quite correctly states the problem "overload", I would suggest the following:

1 - Turn off automatic formula calculations in Excel (for example http://www.howtogeek.com/162219/how-to-change-the-automatic-calculation-and-multi-threading-features-in-excel-2013/)
2 - Switch to 64 bit version of Excel (can use more RAM, and hence, theoretially more tolerant of larger spreadsheets)
3 - Switch to a proper database system. You might even find switching to Microsoft Access or SQL Server Express might give the performance boost you would like to get. You can still use Excel Pivots and Excel PowerPivot on top of these database systems.
[ fanpages ]IT Services ConsultantCommented:
Hi Ian,

I've concluded the software is not suitable for such large spreadsheets described below.
Records often in excess of 100,000 (rows) several columns of formulae.

Further to MlandaT's suggestions for tackling any issue with speed/performance, there is really not enough information provided to give any tailored advice to your specific workbook.

The number of worksheets (& the quantity of columns & rows in each), & the number of columns that contain formulae (plus some examples of these) would be useful to give you better advice.

Also, which version of MS-Excel, & which operating system you are using, may help.

Additionally, what is the file format of the workbook, & is it only using in-cell formulae; is there any use of Visual Basic for Applications code, or third party Add-ins?

I'm using a Dell 7500 Workstation with 24GB hard drive and 320GB free space on my hard drive.

Presumably that is 24GB of RAM.

Do you know the CPU chipset that is installed in your Workstation &, hence, how many (co-)processors are available to MS-Excel?

MS-Excel 2013 - Excel Options - Advanced - Formulas
Have you checked the Master Process List (via "Task Manager" if you are using MS-Windows) whilst the workbook is calculating to see how much RAM is being used, & what percentage of the CPU is being utilised?

Task Manager - Apps
Ian BellretiredAuthor Commented:
Thanks Fanpages and MlandaT
I did mean 25GB Ram, sorry.
I also checked and found my version is 32 bit 2010 Home Student.
There are 8 co-processors working during calculation and
despite that the computer almost shuts down and not enough resources to open pages
or even open the task manager.
It appears the version I'm using is antique and need to update to
version 2013 64 bit.
Could this then handle around 600,000 cells running formulas at the same time ?
Speed for me is essential as I run many simulations in a short space of time.
I believe there is a new version of Excel coming out and wondered how much more powerful
it would be compared to 2013.
Thanks again
Ian
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

MlandaTCommented:
In my opinion. 600,000 is quite a lot. I really would investigate the use of a proper database system and a different approach. Excel calculations are done cell by cell, a database can operate on sets/blocks of data. Excel recalculates everything where a database might be designed to cache precalculated results and not redo everything all the time. A few years ago, I helped a client deal with this exact same thing. Excel was just taking too long to just open the file. Let alone work with it. The approaches taken for the calculations are different between excel and a proper database system, but it's worth investigation / evaluation.  

I wonder whether Google Spreadsheets or Google Fusion Tables are not options? Spreadsheets maybe not, performance might suffer because the thing things in a browser, but maybe fusion tables? If you could (barring privacy and IP considerations) post a sample of the file? So we get a feel for the sorts of calculations your are doing in there... We can advise better.
Ian BellretiredAuthor Commented:
Hi,

Here is a sample as requested.
Perhaps if you have a later 64bit version of Excel if you wouldn't mind copying it down for around 100,000+  rows and see how long it takes to calculate.
Many thanks
Ian
EE-sample.xlsx
[ fanpages ]IT Services ConsultantCommented:
Hi Ian,

I used MS-Excel 2013 (32-bit) for Windows (running on MS-Windows 8.1).

My laptop is on a "balanced" power setting, so neither "Best Performance" or "Best Battery Life"; midway between each.

I opened your workbook, & copied down row 15 (columns [A] to [Y]) to row 110,000.

I appreciate the following recorded times are not that accurate (as I neglected to record the number of seconds past each minute), but I just wished to see what kind of performance was being achieved without making any changes to my environment (& introducing bespoke timing routines into the workbook, or external to MS-Excel).

I also had Mozilla Thunderbird (38.2.0) & Mozilla Firefox (40.0.3) running concurrently (where I was typing this text as the calculation progressed) &, of course, Task Manager to monitor the readings of memory.  Each of these applications would have been checking e-mails &/or changes to web sites (& other various Internet/World Wide Web-related activity at least every five minutes), so there was Network activity during the calculation time as well.

The calculation started at 8:04am (UK time) & finished at 8:17am.

30% had completed by 8:08am.
50% done by 8:10am.
70% by 8:13am.
90% at 8:15am.

During that time the CPU utilisation for EXCEL.EXE went up beyond 97%, slowly creeping towards 100%, then dropped back down between 64%-66% (after 3.5 minutes) & stayed there until the calculation had finished.  As you saw from my screen image above, 8 (co-)processors were being used to calculate the workbook.

On my 24GB RAM laptop (Intel Core i7-4860HQ 2.4GHz) the highest memory utilisation was 300.8MB (12%).

After saving the (.xlsx) workbook, the resultant size was 19.5 MB (20,453,774 bytes).

I don't mind performing more tests if you think it will help, but as I said above, I didn't think this many calculations (based on the rows & columns of in-cell formulae you mentioned) would take anything like the amount of time you were finding.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MlandaTCommented:
Just at 30,000 rows, it's already quite slow on my computer (16GB RAM, lots of free space, i7 Lenovo). Looking at the formulas, it already seems to me that a RDMS might work here. However, there are the uses of INDEX which does not have a direct equivalent in SQL. Use of INDEX implies calculation of cell and range addresses where SQL does not deal with the concept of numbered rows and columns and the use of indexes to get to a value. SUMPRODUCT and SUMIF can be easily handled. Despite that, I would certainly give an RDBMS a go.

Quick Question: How do values in this Excel change? Does any row change or are changes mainly at the bottom... adding new rows? This affects how certain things could be done (from a database design perspective) in a SQL Server environment.
MlandaTCommented:
The comments by [ fanpages ] correlate with my finds. My 30,000 rows didn't take more than 5 minutes... I didn't time it as well as he did though.
Ian BellretiredAuthor Commented:
Hi Guys,
I'm impressed with your efforts. Firstly I am using 32 bit 2010 Excel Office Home and Student edition and bought this at the time to function with a 32 bit database (FoxPro). My system maxed out anything over 75,000 rows and either wouldn't finish or returned some inaccurate values.
I wondered if there is a good deal of difference in speed by using a 64bit version of 2013 as you [fanpages] mentioned you were using 32 bit. ?
I may have been a bit out with my 30 minute estimate as I had done several unrecorded runs over differing row sizes up to in excess of 150,000 rows. In any case even the 13 minutes it took {fanpages] is still way too long as I need to do around 20 or so tests over a 2-3 hour period. Hence my question how much quicker would 64 bit perform.
Regarding your question MlandaT, I would not be adding rows and I suppose any row changes if I understand your question correctly.
I'm not sure how an RDBMS would work, 'scuse my ignorance as I have never used a data source with Excel. I do have SQL Server 2014 installed.
Thanks again guys
Ian
MlandaTCommented:
You can get a 64bit trial of Office here: http://www.microsoft.com/en-us/evalcenter/evaluate-office-professional-plus-2013 . What complicates things is the fact that you have to first uninstall your 32bit version. However, this is one route to see how well the 64bit Office will behave with your data.

Switching to an RDBMS would be a fairly sizeable exercise (time, budget, testing, user interfaces)... probably falling outside the scope covered by this question...? I would exhaust all other considerations before going this route.
aikimarkCommented:
1. Please describe some of the formulas you are using.
2. Please describe your "tests".
3. Are your formulas references as tight as possible?
4. Would sorting your worksheet/data simplify your formulas?
5. Save your workbook in xlsb format and retest the calculation performance.
6. Are there any user defined functions involved in the calculations?  If so, please post that VBA code.
7. Are these formulas cyclical?
8. Are you invoking the solution finder?
9. Do you have any add-ins in your workbook?
10. Is it possible to break up the data onto separate worksheets?

You might try the spreadsheet control from Grape City if
[ fanpages ]IT Services ConsultantCommented:
aikimark: Have you read any of the ongoing discussion within the thread before commenting? :)

Most of your questions can be answered by opening the sample workbook.
aikimarkCommented:
@fanpages

Yes.  I read the thread before I posted.
No. I have not yet opened the EE-sample workbook posted by the OP.
Ian BellretiredAuthor Commented:
Thanks guys,
I will install Excel 2013 64Bit and hopefully this will suffice.
If not, at least I have the latest version.
I wonder when 2016 will be available ?
Ian
[ fanpages ]IT Services ConsultantCommented:
Hi Ian,

Thanks for closing the question.

I think we can probably improve on the in-cell formulae of the workbook; maybe not to the extreme measure of re-coding with a backend database, but certainly looking at a few of the formulae to make speed/performance improvements.

Also, if you would like to raise another question, & state exactly your requirements, the you may find a few "Experts" could offer programmatic (Visual Basic for Applications) code to address these.
Rory ArchibaldCommented:
I wonder when 2016 will be available ?

It starts rolling out tomorrow (22 Sept) as it happens.
Ian BellretiredAuthor Commented:
I have just downloaded a trial for 2013 64bit  :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.