Eight dangers engineers, modelers and analysts need to be aware of when using Excel spreadsheets
It’s pretty obvious why we use Excel so much – it’s everywhere, it’s initially easy, and it’s what we know. But when engineers and scientists look to Excel for performing modeling & simulation, data analysis or any complex scientific calculation, it can become quite the liability – for you, your engineering department and even your company. This is especially the case when Excel is asked to be something it was never really intended for, namely a collaborative tool (and it is asked to be one a lot!).
Here I am focusing on the “big picture” – the high level enterprise view rather than the detailed inner workings of Excel itself. I am also speaking to those cases where there is significant technical, financial, legal, or regulatory exposure due to spreadsheets, though everything here can apply to the simplest cases as well.
For folks interested in detailed internal limitations, for example column-row architecture, unwanted data conversions, problems with using unary minus and so forth, there is much that can be found on the web. I found these two useful: a report published by Eva Goldwater at the University of Massachusetts entitled, “Using Excel for Statistical Data Analysis – Caveats” or for a much more extensive discussion of the pitfalls of using Excel, with many additional links, is “Spreadsheet Addiction” from Burns Statistics.
For the engineer, scientist or business analyst involved in modeling and simulation and other complex mathematical calculations, particularly when these models are used in the aforementioned “collaborative” mode, the bigger picture involves the far reaching consequences your spreadsheet can have on other parts of your organization. Let’s list off the eight most common problems:
Eight common problems for engineers, modelers and analysts when using Excel
Here are eight common problems that engineers, modelers and analysts may encounter when using Excel spreadsheets.
Typing mistakes, errors in formulas, formulas mistakenly converted to values, etc. These can be very insidious and may not reveal themselves…until it is too late! Various estimates suggest at least 80% to 90%+ of all spreadsheets have at least one error.
Lack of IP security
Your company’s IP embedded into the formulas, VBA and the overall workbook are inherently unsafe, as Excel is easily hacked and files can be duplicated, and disseminated unintentionally. Imagine if the formula for Coke was put into an Excel file!
When Excel is distributed to multiple users it can (and often does) fail due to simple mismatches in Excel versions. Furthermore, people are increasingly using mobile devices which adds another layer to the deployment mismatches.
Lack of version control
It is very difficult to guarantee everyone is using the same exact spreadsheet, as they often become edited along the way, intentionally and unintentionally. I think most of us are familiar with this!
Lack of auditability
It can be nearly impossible to determine which version was used by whom and for what specific calculations.
Difficult to use
Other than the authors, the haphazard layout of inputs, outputs and labeling can often make an Excel spreadsheet difficult and error prone for those unfamiliar. User documentation quality can vary greatly.
Very difficult to restrict access because the spreadsheets are so easily transported and updated.
Many engineering processes involve Excel being used in conjunction with other programs such as CAE, databases and in-house codes. Excel is not always easily integrated into a process.
Some of these problems can be minimized or made tolerable, but the options available are limited and many only address a select subset of the eight issues discussed. Additionally, some options can be rather unpalatable and not terribly realistic. We have seen companies try to stop using Excel (good luck), others trying to “discourage” Excel use by their engineers, and some who invest in supporting tools (Google Docs, SharePoint, etc.). In extreme cases organizations resort to building or buying various bespoke products and throwing away the entire collection of spreadsheets built up over the years. Needless-to-say, this approach can be painful, expensive and time consuming.
If you want to eliminate all eight of these problems, and not throw away your Excel spreadsheets, then one tool, EASA, is uniquely qualified and proven in the market to make this possible. EASA is a model deployment platform that is used to “appify” any number of software programs (Excel, MATLAB, CAE, pharmaceutical and chemical modeling, in-house, etc.) and safely and reliably share them at the enterprise level as browser accessible and access controlled web apps. If you have high value spreadsheets that could be leveraged by more users but are kept under lock and key, or if you have some or all of the eight common frustrations, then EASA is well worth checking into.