Nicely put. It's actually rather agile: Excel becomes a prototyping tool to allow the business users to describe what a solution looks like, helping to guide development of that custom solution. Sadly few IT organizations are confident enough to trust their users and work like this, instead starting from zero with a pedantic requirements gathering process before building something less flexible and useful. The problem is partly a lack of domain knowledge in the internal apps team (which is understandable), and partly a kind of technology-first arrogance which prevents that team from making use of the intellectual capital originated by the business in their spreadsheets and processes (which is inexcusable really).
Ideally, an organization comes to understand that Excel is a fantastic tool at the frontier where the business needs to adapt rapidly, but once a process is fixed, replacing it with a fixed system is worth the tradeoff in reduced operational risk.
To your second para., much of that falls to internal apps to provide decent RESTful APIs across their systems. Some companies are doing this, in the process getting to a point where the Excel frontier is just analyzing and reporting on data, not acting as a source in its own right. Then you have traceability for every data point in the organization, and you're in a pretty sweet spot operationally.