There's an immediacy to a spreadsheet: the user can start with very literal actions on data, and slowly introduce abstractions like formulae. In conventional programming, the programmer has nothing but abstractions with which to work.
It's reminiscent of one of Fred Brooks' remarks about how showing the data structures makes the algorithms obvious, but showing the algorithms reveals little about the data structures.
I worked at a place that used Google Workspace and the most effective people simply supplied their own Excel, because it's just way more efficient than spending hours learning how to use Google's clunky pivot table function, when you already know how to do it well with Microsoft.
Every single attempt to migrate to something else has been a comical failure. On the plus side they tend to be rapid failures rather than SAP style multi years
Other commenters have covered the major reasons Excel is so popular. But Excel really shines because it's designed for business. I use R and RStudio for a lot of my work, and while it's great, there are little things that it can't do that Excel can.
I can't leave Excel for that. I can set up a "data integration" in 3 hours that has a highly customizable and (relatively) bug-free front-end, and maintain it myself. The amount of work and knowledge it takes to get the same thing spun up in a proper language with a proper server is 1-3 orders of magnitude more.
The fact that all these tools eventually fall apart at scale is basically irrelevant. You can only take away control from their cold, dead hands, and they will never learn the programming skills to build something more scalable themselves. All solutions almost invariably trend towards shifting scaling problems away from the desired frontend that keeps control (or at least the illusion of control) in the hands of business stakeholders (one of the reasons why companies love building all manner of integrations on top of Jira).
And let's be clear, it's not like the alternatives here (i.e. databases) are so easy. Finance folk are used to creating new spreadsheets on their local computer for free and at will; most databases require setting up a server, DNS, certificates, firewalls, most of which have real costs. SQLite naively sounds like a reasonable approach, but by default a table is limited to 2,000 columns while an Excel spreadsheet is by default limited to about 16,000 columns, and yes, stuff like this really matters when you're talking about trying to uproot a favored tool. At the end of the day, most of Excel's limitations are due to attempting to cram a spreadsheet into a single file (same as SQLite); if Microsoft were smart, they'd offer a cloud-only "spreadsheet" (really a database over a full filesystem, or maybe over object storage) without the limitations of ordinary Excel spreadsheets, where Excel-the-desktop-app only downloads to the local client the relevant cells that are actually in view, while adding more options to mirror/load data from external sources into other sheets attached to the same cloud-only "spreadsheet".
I have seen it used for everything from configuring aircraft for sale to quoting bond prices for voice traders.
There's even that famous Japanese artist who uses it for painting.
It's sort of like the OG normies Jupyter notebook.
I even have done this first day's advent of code, and the first part of day two, in a Google sheet. Formulas only, so no scripting needed.
Yes, it's awful in many ways, but it is very accessible to people who don't consider themselves programmers. "I just want to do my thing" is very easily done in a mashup of the spreadsheet and VBA.
We also forget the pain of learning a new technology. People whose first experience is excel also go through this. Shit doesn't do what you wanted. After a while, they can build stuff in excel, but they don't want to learn python, because there's more pain coming.
Most senior devs have transcended particular languages or technologies, so they don't see it. They pay a very small cost for picking up a new tool, so they scratch their heads when they see a guy who wants to run a trading book on a spreadsheet.
It also chokes on sheets with only a few hundred thousand rows on my 16gb work laptop, something a real database can easily do.
I blame it on Microsoft making such a mess of access that people didn't understand it and started using excel as a database.
I'm sure there are many orgs that would love to ditch O365 for Libre or Google, but can't simply because there is no real alternative at this point. Excel is too entrenched.
Excel can take people from fairly naive "help me make a table of numbers" tasks all the way to actual programming. Via PowerQuery, Excel gives end users access to data warehouses directly. Sure, a tiny bit of SQL is helpful here, but it's not required. I've written no end of data sanitization / transformation tools using Excel. Sure, I would have RATHER done it in Perl or Python, but Excel can be assumed to be present on the target user desktops; not so with Perl.
It would be cool if there was a better off-ramp for advanced Excel users into more focused tools appropriate to their domains (e.g., R, or Tableau/PowerBI), but if these folks are solving their problems who are we to push?
Then came 1995, M/S released windows 95 and at the same time they had a full software suit running on W95. It took Lotus a while to get a W95 version out. I think the same happened to Word Perfect.
So here we are, too bad Libreoffice was unable to do the same with Linux. But now people are so entrenched I doubt they will only change if forced.
Also, years ago IBM tried to get people on LibreOffice and off M/S, it failed miserably. Many Orgs. in Europe is trying now, I hope they succeed.
BTW I wonder if we are heading down the same path with github ? I hope not but seems we could be.