The way into Excel hell - and how to get out again
You know this situation: Some time ago, you used an Excel spreadsheet to do resource and capacity planning for your department or to create a detailed project plan. With each extension, your workbook became better, but also more complex. Additional formulas were added, you built in parameters. A beautiful Excel tool was created. You're proud of it and promote it internally - and over time, you're not the only one using the file, colleagues also love it and work with copies of it. What starts now is the creeping way into Excel hell. Why? What is so dangerous about it? And how do you get out of it?
"Excellent!" you think to yourself as you've become an Excel pro over time, integrating complicated formulas, custom styles, and maybe even external data with macros across various spreadsheets. "It works, it doesn't crash, and even my colleagues use it." Of course, that's true to a certain extent. They have spent painstaking hours linking large amounts of data or creating templates that finally make it easier for colleagues to perform complex calculations and calculate key figures, taking into account every conceivable framework. But the more this Excel tool spreads throughout the company, the greater the risks become. Sounds paradoxical?
Stupid - I broke my Excel!
That's how the problems start: Many users of your lovely solution are unfortunately not Excel professionals like you. One wrong click, one mistype, you get distracted and "Oops - why am I in this weird formula now?" Not everyone who works with the Excel document you've created understands or needs everything, either. So it can happen that "useless columns and rows" are simply deleted. And annoying formulas ("Formulas? Aha, those funny characters?") are also deleted. "I don't understand, so get rid of them." May not be so bad, because someone will have an original file and "Then you can certainly always copy the missing thingsback in".
Your Excel tool goes viral, and with it the errors
However, it only becomes particularly risky when a file in which formulas and column and row structures have been edited (often by mistake, but sometimes also deliberately) is then forwarded and used as a template. This usually positive viral effect then has a devastating effect, because the recipients also work with it, perhaps change the file further, want to "correct" something with good intentions and include a new, but wrong formula. They don't even know where the original formula came from and what the reasoning behind it was. At this point at the latest, the Excel file has lost its added value and does more harm than good - namely when wrong formulas also produce wrong results. The employee lulls himself into a sense of security, because after all he is using a template from a professional, which others also use and "will be alright"...
The risks in Excel hell are manifold
The above are just a few of the things that can go wrong with Excel. But there are many more:
- Consolidate changes: Maybe there is a "master", e.g. an employee in the PMO or a controller, where all edited files end up and who is responsible for the consolidation. This is a very time-consuming and tedious job for a human, and there is a high risk that they will overlook an Excel file, not catch the most recent version, or manually transfer data incorrectly. A machine could typically do this better.
- Lack of historization: Change tracking lets you see per file who made the last mutations. But across the entire body of Excel files, there is no historization and thus lacks auditability across the entire data set.
- No code repository for extensions: Every Excel document grows with time. But if several users have good ideas for extensions and want to implement them, they will never work on a common state. There will always be someone "lagging behind" and possibly working on an outdated version.
- Lack of usability: As controllable as Excel is for experts, as confusing it is for the inexperienced user of a form you have deposited. It is almost impossible to make an extensive Excel document understandable for "simple users". Often the spreadsheets are simply cluttered and unsightly. The user sometimes does not even know what to do, because explanatory texts or help systems are also missing.
- Problems due to content extensions: It is not uncommon for rows to be added in the middle of a table. This causes a shift of the contents in the cells below. And this can have serious consequences. If complicated formulas are stored, which even apply to different spreadsheets, it is essential to check whether these formulas are still correct. This check can be time-consuming, but it becomes even more time-consuming if it is omitted out of ignorance or "nothing will happen".
- Failure to check algorithms and formulas: When a new formula is added or a macro is programmed, it should be reviewed by someone else. The 4-eyes-principle ensures that no mistake was made and nothing was forgotten.
The fact is that many department heads and managing directors blindly trust the creators of the Excel sheets and believe that the information they contain has also been calculated correctly. At the management level, the risk then accentuates: Business-critical decisions are made on supposed facts from a zoo of various Excel tools. And no one notices that the data basis, when it has reached the top of the decision pyramid, is very likely to contain many errors.
Incidentally, the website of the European Spreadsheet Risks Interest Group documents a large number of cases from the business world in which large companies have been doomed by Excel hell. Millions of euros, dollars or francs were lost because errors crept into Excel files and multiplied on the way up. It's worth bearing this in mind.
The good thing about it is that if you've identified these risks in your Excel documents, you're well on your way. But it's even better if you take measures against them.
The way out of Excel hell
The goal of this article is not to make Excel bad. After all, Excel is not bad per se. Quite the opposite. But it has to be used for the right purpose. To put it a bit bluntly, Excel is a file-based single-user application. As soon as several people are working with several documents, which may only be edited in parts, Excel is definitely the wrong solution. With cloud-based solutions such as Office 365 or Google Sheets, this problem is somewhat alleviated, but most of the above-mentioned problems remain.
The scenario described above is still an everyday occurrence in corporations, where many plans and calculations are created using file-based Excel. If the Excel files are or become faulty (which happens quickly), this leads to a series of incorrect calculations, and no one notices - how could they? Formulas and dependencies on other data could be stored in such a way that they are protected and cannot be edited further. And in such a way that not even the most IT-savvy employee can "hack" this write protection to "quickly create a temporary, not so important document". Because then the path leads back to hell. So this is not really the solution, because Excel is and remains in any case a file-based solution, which fragments the data stock.
The way out of Excel hell is clear: replacing all Excel planning templates with a database-driven but at the same time very easily accessible, role-based web application. "What? An entire piece of software right now?" For those who are now raising their eyes - stay calm... We are not talking about a software that has a feature set like Excel itself, but a solution that manages your calculations in a controlled way and ensures the integrity of your data set. A software that provides the right views to each user, manages the data centrally and provides the same answers to all users. A solution that lets you regulate who among your employees can make appropriate changes. Simply an application that improves your processes, takes care of data management and leads you and your company out of Excel hell again.
About the author
Dr. Daniel Hösli is Managing Director and Lead Consultant at INTRASOFT AG, whose SaaS solution PQFORCE is the leading platform for agile, project-oriented business management. He has been involved in the development of project management systems on a daily basis for 15 years in a consulting and project management capacity - both organizationally and technically - and thus has the experience from countless contacts and tasks from a wide variety of companies and different management levels.