I recently had cause to take a beginners course in R - a language I'm fairly familiar with.
One of the other students had never used it before, so we were buddied up in order for me to show them the ropes.
The first lesson of R is always the same. Read a CSV, manipulate it a bit, draw a graph. We did it all without much fuss - and a graph appeared on screen. Nifty!
"I don't get it," the student said, "Why wouldn't you just use Excel for this?"
To a programmer, it seems obvious - but it's a fair question! If you have a static set of data, you can drag your mouse over it, hit a few buttons, and a graph appears. Much easier than wrestling with esoteric syntax in a text-based interface. This isn't a Koan - where the student becomes enlightened at the end. It's a tricky question to answer. Here are the reasons I gave - feel free to add your own.
How do you see the code inside an Excel document? How do you tell exactly what is going on? You have to go clicking through cells, or reverse engineer what settings a graph has.
With something like R, you automatically have all the code visible in front of you. Reading through the code in a linear fashion is possible. You can trace exactly what the code is doing without having to worry about whether there's some code hidden in cell Z44.
Related to the above, it's hard to visualise what changes have been made to an Excel document. I don't know any way to easily see how a formula has changed. With R and Git (or any other version control system) you can see exactly what has changed from one version to the next.
Typically, a user draws a graph on a single Excel document. If you want the same graph of a different data set, you're out of luck. You can copy the data from one Excel sheet into another - but there's no way to easily copy a bunch of manipulations and graph configurations to another document.
With R, you just change
read.csv("2.csv") and the exact same calculations are run on two different data sets.
Related to the above, you can read every CSV in a directory and produce a graph for each of them. You can read data from an API and run the same process on it that you did yesterday.
Excel has a wide range of graphs available - but R has more. Excel can do basic analysis - but R can do extensive, complex analysis. Excel has some decent tooling - but R has thousands of libraries which can do a bewildering array of clever stuff.
Those were the advantages that I could think of on the spur of the moment. Perhaps you can think of more.
But what I learned was that it is decidedly non-obvious why a user would want to use something like R or Python when Excel (seemingly) covers all the basics.