Three Common Excel Spreadsheet Problems and How To Solve Them

Posted on July 28, 2021


Excel is universally loved for its flexibility, power and agility, but probably nearly as universally loathed for three problems it can create when users attempt to share critical spreadsheets, namely:

1. Lack of version control

Most of us have personally experienced the scenario of losing track of which copy of a spreadsheet is the correct and most current. Untold corporate hours have been wasted in the ensuing chaos.

2. Proprietary information or intellectual property is nearly impossible to protect

More untold hours spent trying to lock down formulas, calculations and data, only to be easily defeated by a few simple hacks. In fact, many companies would love to make greater use of Excel, but fear of insecure IP limits deployment. Related to this is the inability to fully prevent undesirable changes, whether to proprietary logic, formulas or data.

3. Inability to conduct audits or analytics

If you need to look at usage metrics or history of a spreadsheet, good luck. Trying to mine data from deployed spreadsheets, again, good luck. And attempts at aggregation can quickly lead to aggravation as hours of cutting and pasting are wasted when incorrect versions are used, errors introduced and/or propagated, or a computer crash suddenly takes you back to step one.

Organizations have tried to address these problems in various ways, for example using Google Docs, SharePoint, and even home-grown solutions, but in the end, if a spreadsheet can still be copied to a desktop (and emailed and reproduced), some or all of these three problems will remain. On top of this, many of these approaches do not work when your Excel files have macros, VBA or complex formulas.

Others resort to developing their own custom software, often at very high cost, with long implementation time, and having downstream maintenance headaches. Some find that major strategic initiatives, like creating customer or supplier portals, or just deploying useful spreadsheets internally are delayed as they try to sort out the Excel issues.

EASA’s solution to Excel problems

Fortunately, there now exists a unique approach that fixes all of this. To better understand the solution, let’s first consider how Excel is used conventionally – on end-users’ desktops. It consists of both a “logic engine”, and also a user interface built on top of the engine – so an Excel file contains not only the user inputs and outputs, but also the logic and calculations to generate the outputs. By separating the logic and the user interface, EASA eliminates the need to download or email the entire spreadsheet containing the logic to multiple users’ computers.

Instead, EASA provides a user interface with only the inputs and outputs, which link in real-time to a master-version of your Excel file on a server. This provides the full functionality of the original Excel spreadsheet, but without the user actually directly “touching” it. Furthermore, the user interface is built as a web app, meaning that it can be accessed and used from any browser (IE, Firefox, Safari, Chrome, etc.), providing vastly superior deployment than physically copying and sending Excel files all over the enterprise.

This approach means everyone is using the same version of the spreadsheet, but cannot see the underlying logic, formulas and calculations, and cannot alter the logic embedded in the spreadsheet. EASA’s smart book-keeping automates the audit trail, keeping track of usage, inputs and outputs by user, and of course version history. Finally, with no aggregation of multiple spreadsheets necessary, this greatly reduces error introduction and propagation while making it far easier to conduct analytics on historical usage. In short, the three most common Excel headaches are completely eliminated!

But there is more good news – many other benefits result from this approach:

1. People using the “spreadsheet” no longer need Microsoft Excel loaded on their local computer, and this opens up access to mobile devices while also eliminating another common problem, which is the failure of Excel to run locally due to trivial mismatches in the Excel release version, service pack, or spreadsheet settings. While “online” versions of Excel, for example found in Office 365 also enable remote access, the native Excel file is still available and open to the user, and this opens the door to the original three Excel headaches.

2. Browser access means that companies can tie access to their internal authentication systems (single sign-on systems such as LDAP, Active Directory, etc.), and control who can access which spreadsheets, and even determine which parts of a spreadsheet a particular user can see. This is valuable, for example when different people need to update different input fields and immediately eliminates confusion on which fields they may be responsible for. Similarly, the outputs can be controlled so that, again for example, only those authorized can see results or aggregated data. Related to this is creating web portals that allow outsiders to access internal tools – for example, a customer can configure a complex product and generate a quote without ever having access to the underlying proprietary spreadsheet.

3. The use of a spreadsheet is often just one step in a multi-step process. Using EASA can enable a far more integrated and streamlined process. For example, with EASA you can launch an EASA quoting or pricing tool (using your existing spreadsheet as the logic engine), right from your CRM system. No more cutting and pasting customer information and proposal information from Salesforce to your pricing spreadsheets, or laboriously generating PDF proposals and uploading to Salesforce. The entire process can be made seamless and error-proof.

While EASA does require some effort to implement, particularly building the web app user interface, note that there is no need for programming – so you don’t have to rely on your IT developers. Anyone well-versed in Excel has the skills to build web-apps with EASA.

For ad-hoc spreadsheets that will only ever be used by a few people, and perhaps also for smaller companies, EASA might not always be the most sensible solution for your spreadsheet headaches. However, for high value, critical spreadsheets that are widely deployed in a medium or large organization, it is a solution which is highly cost-effective, as well as easy and fast to implement.