Many Excel projects start with a small assignment to solve a simple problem. The individual tasked to solve the problem puts together an Excel workbook. As its usage expands, it is distributed to more and more individuals in the company. As more individuals utilize the workbook, they propose enhancements that aim to make it even more useful. With its expanded utility, even more individuals begin using it. As with every tool, these models tend to go well beyond their initial design requirements and slowly become too complex for Excel to handle. As more individuals use the spreadsheet, they start modifying it to meet their specific needs. This leads to multiple versions of the spreadsheet. Fixing a problem or adding an enhancement in one version means that all the other versions must be updated. Ultimately, one of the versions will begin producing different results than the others due to unmanaged changes, which will trigger various audits to diagnose and resolve the underlying issues. This is known as Excel Hell.
At that point, management begins looking for ways to fix these issues. They involve their IT departments, which will frequently provide estimates for custom tooling that would require months to years of development. These solutions are not only too costly, but will take away some of the flexibility that business units enjoy in updating the tools when necessary. In the end, these companies either decide to rewrite the entire tool despite the cost and loss of flexibility or continue using it as-is, knowing the inherent risks.
There is also a third alternative which proves to be far less costly than rewriting, while maintaining the critical segments of the tool in Excel, where business units have control.
One of the main reasons that leads to Excel Hell is the fact that a workbook can be accessed and edited by anyone who has some familiarity with Excel. If you need some tweaks in the workbook, you can simply make the changes and start using it. As more people start editing the workbook, the risk of errors being introduced increases exponentially. Ensuring that only a few individuals can update the workbook will help alleviate this risk.
Another issue is the lack of a centralized change control management system. Since Excel is a desktop tool, users will download a copy onto their desktop and use it without having to re-download any updated models from a shared location. This leads to situations where some users continue using their local copy after the master version is updated, which can lead to inconsistent results. Allowing users to access these workbooks from a web interface (instead of directly from Excel) can help to resolve this issue.
We developed SpreadsheetWEB to solve these types of problems. SpreadsheetWEB transforms those essential workbooks that have become very difficult to manage into web-based solutions.
With SpreadsheetWEB, users no longer access the workbook in Excel. Instead, they access these models through a web-based interface where each user logs into the system and has access to only the relevant portions of the user interface. The web interface exposes the inputs of the workbook as web controls and users can only enter data through these controls. Other sections of the workbook (e.g. tables, charts, formula results) can still be exposed, but they cannot be modified by the end user.
All workbook calculations and logic are handled through the web server. In other words, the workbooks themselves are not directly accessible by the end user – these users can only modify the input values and view the resulting calculations, as pre-defined by the designer. This protects the workbook while still allowing users to execute formulas on the server, interacting with the workbook through the web interface. They cannot modify the underlying workbook and its formulas unless they have administrative-level privileges.
SpreadsheetWEB also decouples the data from the workbook. It stores user-entered data in a database, rather than directly into a file-based system. This eliminates the need for users to store a copy of the model locally. If the underlying formulas are updated, the user data can still be populated into the workbook, making the entire process seamless. The user-entered data can also be used for aggregate analysis and reporting purposes.
Since SpreadsheetWEB can track each user’s activity through its web interface, it provides a complete audit trail. All user activities can be tracked and reported, making the entire process auditable and acceptable by financial auditing requirements.
It is true that most companies have experienced or are still experiencing Excel Hell. Many of them will continue to do so thanks to the ubiquitous nature of Excel. Some companies turn to recoding their entire workbooks despite the huge costs associated with this endeavor. Others will put grueling restrictions around the use of those spreadsheets to reduce the risks of this approach at the cost of overall usability. Our alternative approach features transforming those workbooks into web-based solutions while maintaining the advantages of Excel and reducing its risks. SpreadsheetWEB will do the job at a fraction of the cost of recoding without taking away the flexibility of Excel from business units.
