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 to

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".