• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking for resources / tips on improving excel speed & performance

Hi guys,

Could anyone share any resources they have found useful or areas that look at excel performance and speed. A large part of my work requires building complex models, ran against often huge data sets. I've always struggled with creating models that crunch numbers on such huge data sets in an efficient and speedy manner, often building monstrosities then end up crashing far too often and taking 10+ minutes just to run.

Would love to look at studying what causes excel to rack up size and run slowly, and how I can build formulas & macros that are more efficient, or more ideally look at templates, modeling infrastructures or philosophies for working with large data sets & building complex models

Any resources/tips would be much appreciated

Thanks
J
 
Hi James, why don't you consider using power query which can handle more than 1 millions records in the editor which is using in memory technology, and most of the steps are recorded once (need to build it first time) and each time any addition, updates in the sources, the queries will be updated.
lately I had shared with my organisation and had started converted and transformed many tedious native excel data preparation, cleansing using Power Query, and PQ is mainly UI base and less chance to use code ( M Language for Power Query)

if you can give me a sample of your current excel process and what you want to achieve, PM me
 
The authority on timing issues is Charles Williams (MVP), who has published a suite of timing routines for worksheet formulas and VBA code, as well as advising on efficiency issues.

I do agree with @bluesky63, that a sufficiently large dataset will cause any spreadsheet formula to grind to a standstill. In such cases Power Query and Power Pivot are the recommended tools as they are purpose-written to cope with large volumes of tabular data.
 
There are advanced Excel techniques borrowed from Programming where you use Conjunctive Truth Tables to do Boolean determinations of matching records and then use Index/Match to return results from the source data
These are blindingly fast on Hundreds of thousands of rows and hundreds of fields
They can be incorporated into Named Formulas and VBA for stunning results

Unfortunately the only person who ever taught them was Daniel Ferry and he hasn't answered an email for 3+ years, although you may still be able to join the Excelhero Academy where I learnt the technique

Beyond that is using BI
 
Hi all
Index and match (better than vlookup) although is good technique for getting data from several tables basing on primary key to the resulting table that you want, however still it will be quite slow when handling 50,000 records and above, lately I already shared with my colleagues to use merge in Power query which is super fast in achieving what we want and more, as you can always return back to applied steps to amend (insert other data) without using excel commands

There are other use cases that recently I converted previous excel (those complicated and tedious one) to PQ

However, for straight forward data cleansing, preparation, transformation, we can still continue to excel commands (Index/match, Mid/left/right....), PQ will be useful for constant updating of Source data, repetitive steps that each update need to perform by users (man in the loop), converting using PQ will definitely save a lot of time

In the past I had use DAX in power pivot, it is a rather steep learning for business users (techy will love it), so now there are many options in Excel, natvie excel, Power Pivot / Power Query, PowerQuery / Power BI for visualisation, so data transformation / visualisation nowsaday is much more easier as compare in the past

Cheers !
 
@bluesky63
Were all of your lookup tables sorted on primary key?

Traditionally, exact matches have scaled very badly, O(n²), but, with the newest releases of Excel with XLOOKUP, I believe all that changes. I think Excel has now drawn upon database technique of building in-memory lists of primary keys, so the distinction between exact and approximate search disappears.

I agree that PQ is superior for managing and refreshing imported data but some of the new dynamic array functionality can put Excel formulas back in the frame when it comes to analysis.
 
Back
Top