excel overload

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.

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?


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?

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

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.

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

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.

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

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.

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

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.

@fanpages

Yes.  I read the thread before I posted.
No. I have not yet opened the EE-sample workbook posted by the OP.

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

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.

I wonder when 2016 will be available ?

It starts rolling out tomorrow (22 Sept) as it happens.

I have just downloaded a trial for 2013 64bit  :)