A friend of mine sent me a link to a blog post where a professor in a reputable university makes the case not to use Excel for important work. He points out three major drawbacks of Excel:
- Spreadsheets do not support testing as it does allow some automated tests like unit testing;
- Spreadsheets make code reviews impractical as you need to go cell by cell to check the calculations;
- Spreadsheet encourage redundancies as it is employs copy and paste that lead to difficulties in updating;
He also points that the wide use of Exce is due to its accessibility and makes the recommendation not to use Excel where errors matter.
I agree on the points but I would like to point out some problems with this post. The first is errors always matter - for small and large organizations, people and companies, for small or large scale problems. Even if a mistake for 10 million dollars seems a big one it is probably not that detrimental for company like Google while a 10,000 dollars mistake could be a matter of survival for a small business.
I totally support the cry and efforts for achieving great quality in the models. However, the approach for quality assurance should be adequate to the tools. The unit testing, system validation and other methods that are widely employed in the software development are not applicable in Excel spreadsheets. Excel requires different approach starting from the spreadsheet design, selection of good formulas, organization of the calculation flow, control sums and so on. Although software industry is a good source for inspiration for Excel developers, the methods have to be applied critically. The errors are not matter of the tool but the developer and design and methods applied.
As far as I can tell from the post, the mentioned calculations are on the more intensive part. This brings me to another point - there are right tools for a job. Excel is not good every sort of calculations. I would not use it for a nuclear reactor model. Not because of the potential calamity of an error but the calculation intensity and volume go far beyond Excel capabilities. Tools should be carefully chosen for every job.
Last but not least is the accessibility of Excel as reason for its wide use. Yes, the investments in money and learning time for Excel are very low. However, Excel offers much more than that as it comes with so many other benefits and features that make it more preferable like seamless transferability between developers, lots of features, integration with other tools, databases and so on. The cost/benefit analysis is in favor for it for great number of calculations and models. However, there are many open source and free tools for software development, data bases, ETL tools and many others that could be used in performing all sorts of calculations and operations with data at very low cost. They are affordable but requiring more specific skills and knowledge.
Right tools, right methods, right attitude toward errors - this should be the mantra. I am with the author in his push for quality and will be happy to read his further comments on the topic.