Engineers are World Leaders in Misusing Excel

Your favorite spreadsheet isn’t as great as you think it is. Learn to recognize when it’s holding your data hostage—and what to do about it.

Excel is a wonderful tool. If it were to quit working, the first world would likely come to a screeching halt. However, it’s also been called the most dangerous software on the planet.

Excel offers engineers freedom and flexibility for creative problem-solving that no formal application can match. But misuse of Excel frequently results in inaccurate recommendations and data management problems.

Here are the most common misuses of Excel and what to do when your company falls prey to them.

Misuse #1: Excel workbooks are not applications

Excel workbooks are excellent for ad hoc data analysis. Engineers sometimes forget that Excel workbooks are not computer applications. They lack many of the capabilities we expect of applications, such as:

1. Concurrent multiple end-user access.
2. Robust data validation.
3. Ability to manage significant volumes of data.
4. Backup and recovery.
5. Developed using an integrated software development environment (IDE).
6. Security features such as userid/password or multi-factor authentication (MFA).
7. Data and functionality access tailored to multiple end user roles.

When a workbook successfully fulfills an engineering need, it tends to grow to the point of no longer being manageable and reliable. At that point, engineers should champion replacing an Excel workbook with one of the following:

1. A software package.
2. A cloud-based Software-as-a-Service offering.
3. A custom-developed application, perhaps using a low-code programming language or a data visualization software package.

Misuse #2: Excel is not a programming language

Excel workbooks are excellent for simple data calculations, transformations and charting. However, Excel is not comparable to a comprehensive programming language, even with its VBA and Power Query M capability. Excel does not have many of the features typically expected of a programming language, such as:

1. The ability to compile the instructions into an executable.
2. Readable code with verbs.
3. An integrated software development environment (IDE).
4. The ability to integrate with other languages.
5. Use of libraries containing collections of functions and methods that are used frequently.

When the risks of an Excel workbook become apparent, engineers should introduce an application based on a comprehensive programming language.

Misuse #3: Excel is not a database

Even though Excel workbooks routinely store data, Excel is not a database management system (DBMS). Excel views data as a few tables with a modest number of rows and columns and a small number of relationships among the tables. Excel does not have many of the features typically expected of a DBMS, such as:

1. Concurrent data access by many end users. Microsoft 365 handles simultaneous access by a small number of end users.
2. Ability to store vast amounts of data.
3. Ability to manage relationships among tables through foreign keys.
4. Ability to manage data integrity through business rules.
5. Ability to manage multiple end user roles. Microsoft 365 only supports view, edit and none roles.

When an Excel workbook cannot handle the required data volume or end-user access, engineers should replace an Excel workbook with an application with a DBMS to manage the data. Basing the application on a low-code language or a data visualization software package is often a good choice.

Replacing an Excel workbook with a Microsoft Access application can be sufficient in rare cases. However, Access and similar limited database tools suffer from various constraints and lack of functionality.

Misuse #4: Excel workbooks cannot share data

As organizations pursue digital transformation, they increasingly understand the business value of their data and the value of integrating data from various databases.

Critical data stored in Excel workbooks on network drives cannot be robustly and routinely integrated with other databases. Some organizations attempt to routinely convert and refresh Excel data into database tables with manual effort. This approach leaves all the related systems dependent on brittle Excel workbooks that require constant monitoring and revision.

To improve data accessibility and integration, engineers should consider replacing Excel workbooks with a DBMS application to enable reliable automated data sharing.

What motivates these Excel misuses?

Frequently, these Excel misuses result from a successful workbook that an innovative engineer developed. The workbook delivers targeted business benefits and has grown in size and complexity beyond what engineers can reasonably expect from Excel.

Sometimes these Excel misuses are an attempt to avoid investing in a software package or a formal application. This ignores the risks associated with an unstable or potentially misleading Excel workbook.

Sometimes the organization operates with arduous bureaucratic software selection and development processes that cause middle management to continue using an Excel workbook rather than become embroiled with the IT department.

Sometimes the engineer who developed the Excel workbook doesn’t see any risks because they are on hand to address issues as they arise. That approach can be accepted until the engineer is promoted, transferred, headhunted, fired or laid off. After this event, the risks turn into reality and the need to replace Excel workbooks becomes urgent.

How to check if you’re misusing Excel

Start by identifying the Excel workbooks that provide input data for key work processes. Reporting processes are an excellent place to start.

For the identified workbooks, conduct a risk analysis by considering the following questions:

1. Would there be significant consequences if the workbook results were late or wrong?
2. Does the workbook contain significant amounts of data, likely in the hundreds of thousands of rows and many columns across all worksheets?
3. Does the workbook consist of many worksheets (e.g. over a dozen)?
4. Does the workbook contain SQL statements that import data from multiple databases?
5. Does what is generally assumed to be one workbook actually consist of a set of linked workbooks?
6. Does the workbook use VBA routines?
7. Does the workbook use Power Query M or JavaScript routines?
8. Does the workbook use .xls rather than .xlsx file extensions?
9. Does the workbook depend on routinely entering data manually from external data sources?
10. If the engineer who developed the workbook disappeared, would the business suffer adverse consequences?

How to reduce your Excel risk

The more times you answered yes to the above questions, the higher your risk. You can take action to reduce your risk by:

1. Initiating a project to replace the workbook with a formal application, perhaps based on a low-code development platform (LCDP).
2. Training someone to provide backup to the key engineer who developed the workbook.
3. Upgrading the workbook to use best practices for Excel (for more info, check out Excel for Engineers, Part 1: Avoid These Common Risks and Excel for Engineers, Part 2: Follow These Best Practices).

Excel is a marvelous tool. Misuse of Excel creates risks that many organizations don’t recognize. Taking action to reduce that risk is low-cost and will advance your digital transformation initiatives.