The Package That Never Grew Up: Microsoft Excel
The Package That Never Grew Up: Microsoft Excel
Lesson 1: A Spreadsheet is Not a Database
Microsoft Excel is a strange little package, and that has never really grown up. It is was created in a world of DOS and Windows 95, and still has the same old back-end. It worked well in the 20th Century, but should now not be used for anything simpler than your tax returns. Its integration with other software harks back to the 1980s, along with terrible security and non-existant access control. If you think that Excel is wonderful in corporate level applications, perhaps you should stop reading this article?
Look what happens when I save a file named “test01” to a different place than another file with the same name:
Excel is great for doing little forms and in quick calculations, but for doing anything more complex, you move to Python and a proper programming language. And you soon learn that it has a horrible way of concatenating strings and that you have a limit in the string you can create for each cell with CONCAT():
And you also soon find that you just can’t keep going with your columns:
And with an XLS format, the number of rows runs out of steam about 1 million:
And the whole back-end with VBA is a complete disaster area. Honestly, have you tried to write a simple program with VBA in Excel? It is terrible! In fact, no one uses it, as it is such as secuity issue (and was responsible for many viruses of the past). For forms, the Internet has moved on from created a simple interface with an Excel back-end. And then you look at the back-end for mail merging, and sharing data, and you go back to the 1980s. Basically, it is caught in the past with its history, and it’s never grown up.
The XFD nightmare
Okay. Here I go. In the 1990s, I wrote programs using VBA and with a spreadsheet, but I soon found out that Excel cannot be trusted as a data source. It has a problem in scale, and, while it looks as if you can use any numbers of columns, it stops at XFD (16,384 columns). I also found that the simple Microsoft databases (SDF) have limits on the length of the query and the size of the record. Basically, they cannot be trusted as a data source. As a developer, you soon realise that there’s a big difference between user-level software and corporate level software.
And, so in the 21st Century, I build things for the Web, and in the Cloud. I cannot even think of going anywhere near a spreadsheet for anything past processing student marks. But, now we find that the ‘world-beating’ Test and Trace system uses Microsoft Excel. I really wouldn’t expect this type of implementation from our undergraduate students.
This Excel-based design was discovered when 15,841 cases went missing due to the spreadsheet overflowing (between 25 September and 3 October 2020). For a ‘world-class’ track and trace system, it looks more like a first-year undergraduate coursework assignment. The spreadsheet being used was an XLS format, and where each record took a number of rows on the spreadsheet. This meant that the spreadsheet cut-off after a certain number of records, and could not take any more.
Overall Excel can never be seen as being robust in any way, and an error in the data could cause large-scale data loss. For security, Excel is a joke, and was created in a time before security became such a major issue. The password protection can often be easily breached, and users can easily overcome restrictions (often by saving as an XLS file, and removing the security, and restoring). A copy of the file can lead to large scale data breaches, as it is difficult to control the access to it (as all the data is there for anyone who has access to the spreadsheet. And when it comes to encryption, and digital signing anything, it is a complete non-starter. There is a reason that enterprises use enterprise-level software.
When you look at the most popular databases, you see the core data infrastructures built around the big three of Oracle, MySQL and Microsoft SQL:
Newer NoSQL databases are now popular, too, with MongoDB being popular in creating databases with JSON-type data objects. We work with many SMEs, and I’ve not come across one company who would ever even consider using Excel as a data source.
Conclusions
Sorry … but shocking, shocking, shocking use of software. It is not Excel’s fault, as it does what it needs to do. It is an amazing tool for the job it is designed for. This is a human failing, as someone has picked the wrong tool for something that should be the best in breed. We must point some fingers at executive level, as chief executives should have a knowledge of the tools that their organisations are using, and ask the right questions.
There is a large difference between packages defined for users, and one’s that are created for an enterprise level. It’s the reason you use Microsoft SQL for a proper database and not Microsoft Access. I really worry about whether we have really progressed in software since the 1990s.