• +44 (1235) 420123
  • 1 (800) 711-5346

Eight dangers engineers, modelers and analysts need to be aware of when using Excel spreadsheets

by Mike Nieburg in All Blog Posts, Appification, CAD/CAE, Democratization, Simulation, Uncategorized

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:

  1. Errors – 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.
  2. 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!
  3. Unreliable deployment –  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.
  4. 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!
  5. Related to #4 is lack of auditability – it can be nearly impossible to determine which version was used by whom and for what specific calculations.
  6. 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.
  7. Access control – very difficult to restrict access because the spreadsheets are so easily transported and updated.
  8. 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.

Interested in more information?

  • Find case studies for companies such as GE, Monsanto, Pfizer, P&G and others.
  • Join the conversation on twitter
  • Learn how building apps with EASA requires no programming. Watch the video
  • Find out how you can test drive EASA before you commit,  contact us.

 

 

 

 

 

Tags: , , , , , , , , ,

1 Comment

  1. Mitch says:

    You might consider another problem our organization encountered, use of the “share workbook” capability in Excel as a collaboration tool across work groups. We made extensive use of the feature with about 12 people able to work on a spreadsheet simultaneously and discovered subtle issues and/or bugs that sometimes wiped out the spreadsheet and caused data loss. Once the spreadsheet is converted to to a shared workbook, you cannot revert to a normal workbook. If you use this feature, be cautious and make certain to backup the spreadsheet on a regular basis!

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>