Excel is the world’s leading spreadsheet tool with over half a billion users, and is often used as a database for businesses and individuals to store information in. But should it really be used this way?
Well, if this sorry story from the UK is anything to go by, then perhaps not. Back in October 2020, news publications including The Guardian reported that almost 16,000 people infected with the coronavirus weren’t included in Public Health England’s infection counts and contact tracing efforts. The reason? The Excel spreadsheet had run out of space.
And although this is a pretty extreme example, it lays bare the limitations of the enduringly popular software. This is by no means the only reason Excel doesn’t make a great database either, with everything from unwieldiness, security issues, and inability to perform analytics holding it back. Here we take a detailed look at the problems that arise when harnessing it this way, and what businesses and individuals should use instead.
Why you shouldn’t be using Excel as a database
Not suitable for collaboration
While Excel is built for sharing information, it works poorly when true collaboration needs to take place. A major constraint to this is that, in order to maintain integrity, a single, master copy has to be maintained. This normally restricts access to that master to one person at a time typically requiring “checking in” and “checking out” of a document. The result — slowing businesses down and hampering productivity. Furthermore, user error can corrupt the master copy, for example an overwritten formula, and lacking any effective audit and history tracking, hidden errors often arise. These can cause disastrous miscalculations.
Version confusion
Very often the master copy strategy is not employed. Instead, spreadsheets may be passed from user to user for sequential updating, or copied to multiple people for refreshing. This requires consolidating the documents, which is another time consuming, error prone step. In either case, sharing spreadsheets among users can create confusion around which file is the “current” copy. This often leads to major inefficiencies, or worse, the wrong versions being sent across departments, and even to customers, clients or suppliers. Here at EASA, we describe this issue as “version confusion”.
Security issues
Even though it’s such a popular software and created by Microsoft, the world’s second largest tech company, Excel is beset with security shortfalls and is far from being impervious to hacking. A “protected” spreadsheet alone isn’t sufficient to protect files from those intent on extracting the core logic and proprietary data, putting critical intellectual property at risk. What’s more, users are forced to share documents via methods like emails which are easily saved on portable storage devices, and these make them even more susceptible to finding their way into the wrong hands.
Unmanageable
We’ve already shown how Excel is not a good database due to its issues with large amounts of data, and it’s not just the risk of running out of space that can impact users. The software itself is highly manual and extremely unwieldy. Although you can track, sort and filter data to a certain extent, Excel lacks the far more specialized data analytics, manipulation and filtering that proper relational databases have.
Input errors
While Excel provides some data validation, and you can build in some error trapping, these capabilities are limited and often require substantial effort to implement. It’s all too easy for input errors in Excel to derail things for businesses. Just one typo could massively alter the value intended to be put into the spreadsheet, which can have huge financial repercussions worth millions or even billions of dollars. This problem grows geometrically when errors are propagated, as other users access and use these error embedded Excel files.
Why you should use a relational database instead
So, we’ve come to the conclusion that Excel typically makes a poor database. But what should you use instead? A relational database (also called a relational database management system (RDBMS) or a SQL database) is a tool specifically developed for storing, manipulating and retrieving data. It stores a collection of data types that can be connected (related) to one another based on predefined relationships. While, like Excel, it also uses tables to hold this data, the relationships (rules) between these tables can be used to link and cross-reference them. As such, It provides far greater capabilities compared with “flat files” such as Excel.
Relational databases store information more effectively
As touched upon, data in Excel spreadsheets is always at risk of being deleted, overwritten or changed because of human error. This type of mistake won’t happen with a relational database as the data input type is predefined during its design process. Consequently, each table won’t accept information of a kind it’s not assigned to hold.
Relational databases can handle large volumes of information
Although Excel can store roughly one million rows and 16,000 columns of data, this pales in comparison to a relational database. While the exact amount of data it can hold depends on the file size allowed by the particular operating system, it is usually a lot more than the Excel limit.
Multiple users can access the same database
Unlike copying and sharing Excel files, a single and central relational database is by definition the master copy, facilitating collaboration and enhancing productivity.
Relational databases are more robust
It’s usually much simpler to update multiple tables in a relational database than do so across different spreadsheets. In the database, the changes would automatically be made in the various tables, whereas each Excel spreadsheet must be updated manually.
Relational databases offer greater data manipulation and analytics
While data in spreadsheets can be sorted and filtered, the keys in a relational database allow for much more sophisticated querying functions that can obtain all records matching certain criteria. You can also cross-reference data and perform complex aggregate calculations across different tables, which cannot be done with multiple Excel spreadsheets.
So, what’s the catch?
Relational databases sound almost perfect. However, there’s one big caveat. While providing the exact functionality ideal for data, relational databases require specific knowledge and experience to use, and this presents a significant barrier for easy, widespread implementation. In other words, you need to be fully educated in the technicalities of them to operate them properly, let alone build them. If people who aren’t trained in using relational databases need to employ them, it can be very difficult, or even impossible.
How to use a spreadsheet as a database with EASA
Though you shouldn’t use Excel as a database by itself, this is a way you can harness spreadsheets in this way: by using EASA. This converts Excel files to a web app, with the spreadsheets acting as its engine. Accessible through a web browser, the underlying Excel files and logic remain secure within the corporate network (or cloud), meaning users only touch the input data to obtain the resulting outputs, and don’t access the files themselves.
Most pertinently, EASA comes with its own relational database, and can automatically transfer the data held in the individual spreadsheets into this central repository. This then provides actual database functionality at the back end, while still preserving the ability to continue using the spreadsheets as is. Perhaps most Importantly, EASA enables the inclusion of relational database functionality without the need for the expert knowledge normally required; Average Excel users are able to implement this capability.
All in all, EASA facilitates collaboration considering multiple people can use the app at once, offers greater data security as the spreadsheets don’t need to be sent between individuals, and eliminates version confusion and input errors, as users don’t use the actual Excel files. That’s not all though, and there are even more benefits to the solution.
Integrable with a range of databases
EASA can be integrated with many other software and data sources, including databases. It can be integrated with multiple databases, enabling different sources to supply information simultaneously. Other typical integrations include CRM systems like Salesforce, ERP programs such as SAP, and engineering simulation and modeling software.
Removes Excel compatibility issues
Another major issue with Excel we haven’t touched upon is that often different users have mismatched versions of the software or contrasting settings, which can stop spreadsheets from working properly, if at all. However, because EASA runs Excel on dedicated, secure servers, this problem is avoided entirely. All users need is a web browser.
Enables a customized, browser-based interface
EASA is a low code development platform, meaning you can create your own customized user interface without the need for a developer. This lets you quickly create a non-spreadsheet appearing application with greatly enhanced ease of use, best suited to your needs.
Helps you fulfill compliance and regulatory requirements
EASA ensures that the proper, current spreadsheet is always used with no possibility of working with an out-of-date version. It also provides an “audit trail” of usage, such as who employed which app, when, and with what result. Due to this, you’re much better placed to fulfill compliance and regulatory requirements.
Thank you for reading. If you would like to find out more about EASA software or make an enquiry, please don’t hesitate to contact us.
