Why Microsoft Excel Can Be So Dangerous

In 2013, a Forbes opinion piece declared that Microsoft's Excel Might Be The Most Dangerous Software On The Planet. It’s been ten years since that declaration and Excel errors are still causing havoc for many organizations. Following are just a couple examples of how sloppy use of Microsoft Excel caused major headaches in the workplace.

In late 2021 Excel errors and omissions contributed to a recruiting & hiring nightmare for the Anaesthetics National Recruitment Office (ANRO) in England. According to the London School of Anaesthesia:

​… The errors were initially hard to trace. Some members of the team were using the VLOOKUP() Excel function, others were not. The quality assurance processes that were in place did not identify the mistake, so it went unnoticed until a candidate submitted a complaint.

Another example of Excel errors causing business chaos is documented in a November 2022 Bloomberg article.

​Bungled Excel Sheet Hurts Profits From Islandsbanki Sale
 
… A report published on Monday found that officials who oversaw the sales process were not aware of actual buyer demand, because data in the Excel spreadsheet they used was deficient. Some fields that contained the amount of investors’ offers included “foreign commas or amounts defined as text” and, as a result, the Excel spreadsheet did not include such offers as numerical information.

These are just two examples highlighting what so many professionals already know. Microsoft Excel errors are very common, and the bad data can cause serious problems. In order to use Excel properly, it is important to understand why Excel has such a horrid reputation for major errors.

Microsoft Excel is one tool within the data management toolbox. Like any other tool, Excel can be (and often is) misused. Excel is a fantastic tool for examining data, for creating charts to visualize data distributions, and for evaluating datasets. These are the types of jobs that Excel spreadsheets perform very successfully.
 
In addition, Excel is also a very accessible tool to use when collecting information from remote users. Excel can be stored on common Sharepoint sites and used by multiple people in different locations to submit &/or view data. But the very accessibility of Excel, the ease with which one can share information using cloud platforms also leads to misuse.
 
For instance – in the example of employee recruitment given above, where some users were using a VLOOKUP list to populate Excel cells while other users were not, is a very common example of inconsistent data management. The information is being entered in different ways by different users across multiple spreadsheets. Because information is populated inconsistently, errors occur. Then when that data is merged, mistakes are often compounded and lost within the sheer volume of information in the master Excel file.
 
There is inconsistent data management in the second example as well, where some of the currency data included “foreign commas or amounts defined as text”. The first example explicitly states there were multiple users entering data in different ways, but there is also a very high probability that the irregular data in the second example is also a result of multiple users entering information in different ways.
 
So… what is the proper way to use in Excel in collecting data from multiple sources?

This is a very real problem because remote work is becoming increasingly common. The sheer ease with which organizations can throw a spreadsheet up on a Sharepoint site means the need for managing data integrity in shared applications will not go away.
 
For all the advantages Excel provides in ease of collecting and sharing information, this tool is not designed to store and manage large volumes of data. Nor is Excel designed to protect data integrity so that organizations are defended from major data errors. Within Microsoft’s Office Suite, the tool designed to store and manipulate data most efficiently and safely is Microsoft Access. Since Access is a database (and not a spreadsheet), it has built-in methods for managing data integrity that are simply not available in Excel.
 
Organizations set themselves up for major problems when they don’t use each tool appropriately. But it is possible to build top-notch data management solutions using Excel and Access together. Because Excel and Access are both within the Microsoft Office Suite these two tools can be paired to design very efficient in-house data management solutions.
 
For example, I’ve worked with clients to build combined Excel/Access applications for managing employee time reporting. Consider an organization where employees work from various locations and need to report their time to a central accounting department. In this example, let’s assume that each employee is servicing clients and, in reporting their time to accounting, they need to provide the following data points:

• Their own employee ID number
• Client ID (so the activity can be appropriately invoiced to clients)
• Date of activity
• Activity code (again to help with invoicing clients)
• Amount of time spent on the activity

 
In this situation one employee can enter several lines of billable time activities every single day. Each of those line-items will be used in the payroll process, in the client billing process and used for employee accountability. Because this information is important for so many processes, data integrity is essential. So, we must build a solution which is easily accessible in remote environments and also ensures data integrity.
 
A typical solution for the above problem would include the use of Excel for remote collection of employee time data. Then – at the end of a payroll period, after staff members enter their time, Excel data is uploaded into Access for processing in one central silo of information.
 
As a database tool, Microsoft Access has built-in capacities for ensuring clean data. Capabilities which are just not available in spreadsheets. For instance, data tables can be connected to each other using referential integrity restrictions. In concrete terms, this means that if a staff member enters a faulty ClientID into her spreadsheet – that error will be caught upon import into Access. This will happen because, with referential integrity restrictions in place, Access will not upload a time record if there is no corresponding ClientID in Access. The same can be said for data points such as Activity Codes, Employee ID numbers, etc…
 
The ability for Access to refuse import where matching key fields cannot be reconciled is essential for data integrity. Within Access it is possible to build reports which show records NOT imported because of data integrity issues. These reports can then be used to go back and fix wrong entries in the original Excel file or go back to the applicable employee to figure out why there is a discrepancy. Errors are caught in real-time and (most importantly) professionals using this information trust it because they see it being “cleaned” as data is imported into the Access side of the data management system.
 
In addition to cleaning data during the import process, within Access reports can be built for employees to review and confirm that their time is accurately imported into Access. Another advantage of using Access to upload, merge and store all employee time data is the benefit of compiling the data in different ways for different purposes. In this example, the information can be compiled for payroll purposes. Or the information can be used to invoice clients for billable time. Or… Activity Codes and time information can be used to assess and monitor either employee productivity or determine which services clients use most. And - because Access and Excel work so well together, once time data is centralized in a common silo and errors are cleaned out, it is possible to compile the data as needed and export it back to Excel for analysis or reporting needs.
 
By combining the use of multiple Microsoft products (in this case Sharepoint, Excel and Access), it is possible to use the best capabilities of each product to build a solution which works for everyone.
 
Microsoft dominates the office software market because they had the foresight to build the Office Suite so that information can easily move back and forth between different products. In this instance the ease of moving data from Excel to Access provides tools for building a data management solution which works both for gathering data from remote employees, but also cleaning the information as it is imported to a central database and ensuring long-term data integrity.

Michelle Meyer - 02/15/2024

More Data Management Articles

• ​Microsoft Access and Data Integration
• Yes – Microsoft Access works in a Multi-User Environment
• YES – Microsoft Access Can be Used Securely
• Deploying Microsoft Access in a Remote Environment
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.