But the business people had a strategy to defeat this every time. They would simply make sure that "copy/paste" was on the requirements list every 2-year cycle. When the new software came down, they would satisfy the company-wide decree as follows: (1) open the new software; (2) copy the data; (3) paste it into Excel. And then they would continue as before. The only thing that changed every two years was which buggy, slow, and limited in-house application they would be copying data out of and pasting it back into.
There's this dilemma that the people buying the software and mandating its use aren't the ones who end up having to use it. So, often ridiculously complex workarounds happen in order to comply with company policy yet still be able to get any work done at all.
This quite likely is one of the largest productivity sinks in industrialised societies.
There are ways to amend these issues but they require a change both in company policies and in thinking: Allow departments and teams to choose their tools. Have a repository of internally developed tools (Excel spreadsheets, bespoke CRUD applications, shell scripts etc.) for others to share and build upon. Put an emphasis on software quality and user experience.
I would absolutely echo all your points, especially the rationale behind "real software": it would have been far more expensive, have taken far longer to be delivered, and that's if it were even signed off in the first place. When management sees "real software" as wholly unnecessary and all you have is Excel, you soon learn to be rather creative.
Once I was contacted by a friend who works on the QA department of a factory (belonging to a multinational company) to implement a system to keep track of the building process (what was soldered, who did it, what tolerances it must have, things like that). The factory's process was entirely modeled in Excel spreadsheets, with the caveat that they took almost 6 months to gather and deliver the data to the client after installing the product on the client site.
As it was a freelancing job, I gave them a very high hourly rate, such that it they could forget about it, as it was too much work for one person alone. But in hindsight, I also refused because most certainly it would imply that a lot of the workers in that departament would be made redundant (there were 30 workers there, I would say that at least half would be unnecessary after the system was up and running). If only management understood the huge savings that would be possible.. But in this case, I'm glad they don't!
I blame the "MBAs can do anything" philosophy, but it's probably also a consequence of worker mobility.
To state it most simply: if your management (at any level) cannot call bullshit on the data their direct reports provide them (whatever level of abstraction that is), then your business is at existential risk.
I almost had a heart attack thinking about it while watching one of my bosses try to consolidate 13 different versions of a spreadsheet, none of which made sense. And then I realized he's one of the above par ones.
Vast majority of calculations are performed in excel. Most engineers know at least a bit of programming from one mandatory class in first year, but for some it's fortran, others it's matlab, vba, etc. Excel spreadsheets are the common denominator.
Thankfully, most sheets are rather simple, with one calculation per line, with inputs coming from previous intermediate calculations on the above lines.
Unfortunately, sometimes, the logic needs to be more complex, and that's where things go south. Despite all the best intentions, spreadsheets are very hard to test, audit and version-track, and errors will inevitably sneak in.
Other things get harder too, when all information is in spreadsheets. Recently I was tasked with updating and reporting about 200 calculations that were contained in 200 almost-identical sheets. A single factor needed to be changed on those 200 sheets, and then I needed to compile the new results in a summary table: relatively simple VBA macro, right? Except multiple of those sheets were just slighlty different, due to multiple engineers modifying them for slightly different cases. The solution ended being similar to scraping really screwed up html with something like BS. A variety of regexes, string searches, and even heuristics based on cell formatting needed to be used. A lot of information was conveyed visually in those sheets (ie, a new calculation block started with a title that was typically bold, blue, and 20pt, but sometimes a bit different).
The same thing could've been implemented in python/julia/fortran/heck even C, version tracked and unit tested. Verificating and understanding the calculations is way easier, as all the equations are there in front of you when you open the source file. The program could take input written in simple text file (think ini or even yaml if something more complex is needed), then spit out a report in another text file, or even fancy formatted html. Then, future modifications to handle "special" need not a different program (spreadsheet) for each new case, but in msot cases the master program could be updated while still ensuring backwards compatibility, and all calculations could be rerun in batch from the input txts.
Anyways, that's what I dream of sometimes while I'm either doing rote manual excel-scraping or trying to think of a new convoluted heuristic to program it. Unfortunately, the single most important thing for documentation is that colleagues can understand it, and most MEs still see programming as something too complicated and time consuming. I also work in a big corporation, and IT utterly useless. Getting a custom tool made by IT would be a hell of budget approvals, resource plannings and a bunch of three-letter acronyms for at least a year (seriously) before a programmer would strike a single key. Comparatively, a spreadsheet can be bashed out in an afternoon by basically anyone.
This is pretty common practice, in Australia at least.
I do note that you say "mostly" - but I've seen truly epic spreadsheets (including one that my employer at the time spent almost £1 million on) and I'd rather reverse engineer an application with source code rather than a complex spreadsheet (even though the feature in Excel to show dependencies visually is rather cool).
Same thing with reliability. So many of these IT supported applications that are supposed to survive a nuclear strike are down a day a week, while the basic access database pretty much hasn't seen a downtime during business hours since inception.
Tactical initiative: not just for Marines. The closer software is being built to a person who actually posseses the domain knowledge, the less risk of misunderstood requirements / behavior there is.
Readability: a language that requires six months to learn to read but expresses programs in half the length is often times not the right choice. If a good enough language can be read by someone with a week of training, and 90% of the domain knowledge is possessed by people who don't know the better knowledge, then use the good-enough language.
Reasons like these are why Excel / VBA is used. And moreover, why it (paradoxically to us) works.
Sometimes, something gets publicized. Usually, it will get shrugged off and nothing much will happen about it. This makes one wonder when the whole house of cards will finally collapse...
I find this very interesting because my company (which I applied to YC 2017 with) is based on exactly this same premise.
if something goes really wrong, they can fire the intern and caim they went rogue.
it is much harder to shift blame to a department where everything is full tracked, approved, etc.
i think this a symptom of a much bigger hidden secret. corporations are not organized to accomplish tasks. they are organized to avoid responsibility.
These spreadsheets are often put together by an intern or associate who "knows a little VBA" and automates or tracks a task that the business was doing manually. Nobody on the desk really knows how the spreadsheet works.
Changes to the spreadsheets are not controlled in any manner, are not tracked in source control, but are often (thankfully) backed up by virtue of being saved on a network drive.
The business prefers spreadsheets over "real software" because they have complete control of it whereas their internal IT department are slow and expensive, and the resulting custom-coded products are often substandard (see zubairq's comment about paying market rate).