By virtue of Turing completeness there's nothing you can do in Excel that you can't do in a program. It's all a matter of speed.
Having seen Excel wizards work their magic before, the dizzying ways they can slice and dice their data with the help of a combination of GUI affordances, formulas, and hot keys is truly astounding. Often times a person could build out a full set of data and charts in half an hour that might be something like > 100 lines of equivalent Python/Pandas code.
And crucially often the report would have less bugs than the equivalent code because the analyst could see all the data in front of them as they were manipulating it and would naturally do spot checks along the way.
Now note the "some" in that statement. A Python/Pandas master could also probably whip up the equivalent > 100 lines in half an hour. But it was really astounding just how fast Excel experts worked.
Yep. The peculiarities that make this Excel workflow unreasonably effective are pretty easy to identify:
1. Tabular data. There's some tricks with named ranges etc, but for the most part your entire application state is spread out before you, scrollable in every direction. It's just tables, and clicking into a cell highlights relationships (data dependency) between cells.
2. Visible data, hidden code. =macros are hidden behind their result; the most obvious thing is to treat them as tiny black boxes, applying a single data transformation (or small set of logically related transforms), and immediately see the result applied to your data set. This is a tiny bit like functional programming, and a tiny bit like Pandas or Spark (immutable data, lazy evaluations). Except unlike those worlds, Excel pushes the data front and center, not the code.
And prob a bunch more I'm forgetting. It doesn't even feel like programming, more like building data pipelines in Unix or something. Except you can easily preview the data at each step in the pipeline. What I really want is Excel or Siag, but with Python and SQLite and a nice spreadsheet UI.
You've built this brilliant report because you're an excel wiz, but because of that you've gotten someone up the chain's attention and you need to do it every day/multiple times per day, and automating out all your shortcuts, hotkey and ui clicks with macros becomes a horrifying cludge that had you invested in something more automation oriented earlier would produce more resilient/repeatable solutions.
> Often times a person could build out a full set of data and charts in half an hour that might be something like > 100 lines of equivalent Python/Pandas code.
But only ten lines of R! (Excel is kinda awesome though).
Having seen Excel wizards work their magic before, the dizzying ways they can slice and dice their data with the help of a combination of GUI affordances, formulas, and hot keys is truly astounding. Often times a person could build out a full set of data and charts in half an hour that might be something like > 100 lines of equivalent Python/Pandas code.
And crucially often the report would have less bugs than the equivalent code because the analyst could see all the data in front of them as they were manipulating it and would naturally do spot checks along the way.
Now note the "some" in that statement. A Python/Pandas master could also probably whip up the equivalent > 100 lines in half an hour. But it was really astounding just how fast Excel experts worked.