Clearly Excel isn't always the best tool if you are free to choose between R or Python or C or Java. But if you're not a developer then Excel may be the only tool that you and all your colleagues are guaranteed to have. Even if I persuaded ICT to install R on my work PC, my colleagues wouldn't have it so work wouldn't be reproducible.

I find that approaching Excel as if it were a database or a programming language tends to reduce the replication problem. For example to analyse a daily file:

* set up a data connection to an exemplar file

* refresh the connection with the new file daily

* set connection option to copy formula down so each row is identical

* point to a separate spreadsheet for lookups

* use a recorded macro to paste the calculated values into a cumulative spreadsheet

* do pivots and change over time graphs in the cumulative spreadsheet

I don't think Excel is the best tool for this workflow, but a logical approach makes it "good enough".