How far can one go with excel?

Question

in my business we handle all analytics through Excel. This includes mostly scheduling, production planning and accounting operations. We currently are looking into adding a bit of predictive modelling and Excel does suffice to a point, but doesn't have support for complex models.

As I see it, the main benefits of Excel are its ease of use and the ease with which you can find and train people to get accustomed to it. On the other hand more sophisticated environments (e.g. R, python) can handle a wider variety of analytics tasks, but require better trained individuals.

I have also read this question on if Excel is sufficient for Data Science and while it is a bit out of scope for my purpose, the conclusion is that tools like R and Python are much better than Excel.

My question is (in the context of data analytics): "How much far can we get with Excel, without needing to change to a more sophisticated tool?" or "At what point do we need to migrate from Excel to -let's say- R?"

Thank you very much!

My comment, for what it's worth: Excel is OK for relatively small datasets of known size. It's terrible for large datasets, and for datasets of varying length. — Adrian Keister, Sep 13 '18 at 15:59

n1k31t4 · Accepted Answer · 2018-09-11T01:26:05.387

TL;DR

If you have unlimited time and use a 64-bit version of Excel, you can get as far with Excel as any other data analysis tool.

Time

I mention time as my first factor, because Excel only has basic funcitonality built in, such as summing, random number generation, lookups etc. These correspond to a kind of standard library, which Python and R also have. Using these basic functions, with enough time, you can build up pretty much any analysis tool out there. Don't expect good runtime performance. In Python and R, however, there are many many packages that people have already created, which perform well and have been tested by lots of people and so are trusted.

Memory

My second point about 64-bit Excel is because that allows a lot more memory to be used by a single instance of Excel. It allows many more cells to be filled. Using 32-bit excel will limit you to projects of around 2Gb. That is a fair amount of data, but it is a hard limit.

Then steps in 64-bit Excel, which basically means no more memory limits - only those that come from your hardware, and that means Python and R will also be stopped in their tracks.

To provide some numbers, we can simply compute the number of bits able to be stored in each version. Here in Python's interactive prompt:

In [1]: (2**32) / 10**9          # 10^9 means the result is 4.3 Gb
Out[1]: 4.294967296

In [9]: (2**64) / 10**18         # 10^15 means the result is 18.4 Eb
Out[9]: 18.446744073709553

Eb means exa-bytes. This means 18.4 million million million gigabytes.

I notice the computation shows 4Gb for 32-bit, while I said 2 Gb above. I read there is a kind of hard limit on the 32-bit version. I don't know or care why that is... I use Python and R ;-)

In any case, I hope that is enough to convince you that memory is not an issue, if you are a brave person willing to invest all your time building tools from the ground up!

Summary

If you have complicated business logic, where the actual analysis is mathematically simple, stick to Excel. Business people will love you for it.

If you want to do more than linear regression, use Python or R.

Caveats

As far as I know you cannot run remote or distributed tasks using Excel, whereas that is relatively easy using Python and (a little less so in my opinion) R. So at that point, I would give up on Excel. You'd likely have to implement your own tools in C# or C++ using the .Net framework.