I love how approachable it is. Domain experts in other fields are able to program in excel and codify their knowledge. While, not always great in terms of code quality, often it is bad, it still provides a great starting point in moving to something more resilient and safer in terms of data validation.
There is a reason it is the worlds most poplar programming language
The thing is: Excel is one part data store and one part programming interface. The programming interface just happens to be a part of the data store. This leads people who have been told they're not allowed to program to solve the problem with the best tool they can hold: Excel.
I once got an email in my inbox that had the subject "Help: recommendations for machine to run Excel". The body described a person within a business unit who had a laptop, ca. 2018, with a then current I7, 32G of RAM, dual raid10 SSDs, and it had one job: Open, edit, and evaluate one 5+GB Excel workbook.
What happens is this pattern where more applicable programming models are taken from business folk, who need a way to do some level of broad, tabular accounting usually. These individuals don't have Python, don't know Python, or web applications, etc. What they have is Excel.
Excel lets you do something very easily that no other thing in existence lets you do: Real time (ish) calculations against very large sets of data. I can write one cell, `=AVERAGE($A9:$A99)` and get the running average for 90 rows in a table. Imagine the work that would go into doing that in a language like Python or Go, plus the scaffolding to make it work in a webapp.
And so when a business unit says "we need a way to do X", inevitably someone says "there's no budget for that" and someone eventually finds their way to Excel. Then they build the thing in Excel and when someone realizes they have a monster on their hands someone inevitably says "this should be a webapp" at which point nobody has the funding to maintain that webapp plus its infrastructure, plus handling the implicit and explicit logic that comes along with the mixture of data, code, and everything else in an Excel spreadsheet.
I think a lot of the issue with Excel is the developers, often, look down upon it. This is too bad as it doesn't help those using it to solve real world problems and it often leads to them not seeking help. Then we end up with the billion dollar London Whale's.
I don’t look down upon excel I look down upon development practices inherent to excel.
No proper way to collaborate with version control. No real change control either - Joe can have version 1.4 Bob still 1.0 - version from Bob making loses for the company.
No real way to debug like getting to know where cell is used is impossible.
That said excel is powerful and I also use it for personal stuff or stuff only I need to calculate.
When I have more than 5 people using and changing the same excel all is lost - maybe office 365 helps with collaboration but it still needs a bit more features.
That said excel is awesome on its own - single spreadsheet with complex calculation used by more than 5 people is recipe for a disaster.
I use it for quick table to code codegen often. But it wasnt about you personally just the comment’s people make about it. With onedrive people do get a sort of versioning, albeit very crude, too. But your right, the practices are bad and part of it is people are not having conversations about best practices. Also, the cost of development being what it is and people’s incentives, Excel fits into that just get it done and deal with it later spot. I know in my org it was a minimum of 50k to get a dev shop, internal, to do a project. This was years ago too. Too many big fish for them to work on. So the small groups went with what they could use. Also, it is on all machines.
I think with Numpy and Pandas getting the average of 90 rows wouldn't be too hard. With R it's insanely easy: mean(data[9:99,]). I'll admit Excel is a easier for the average joe to get started with but there's a lot of downsides like it being relatively difficult to figure out WTF a sheet is doing at a glance.
It was informative though, but maybe next time precede with something like funny typo. Us as a group are easy to trigger into helping those wrong online :)
There is a reason it is the worlds most poplar programming language