Can Excel function as a database? If so, what are the excel database limitations that people need to be aware of? Due to the tabular structure that Excel offers, there are many cases where the software is used as a database. In fact, many organizations use excel as their main or provisional database to some or all of its data due to its simplicity, wide availability and low cost of entrance. There are some limitations that should be considered before deciding whether to use excel as a database: 1) Size of Dataset; 2) Data Growth Rate; 3) Sharing; 4) Security; 5) Workflow Complexity; 6) Data Management, and; 7) Audit.
At this time, Excel ‘hard’ limitation is that it cannot surpass 1,048,576 rows and 16,384 columns. Even though it is possible to reach these limits, many ‘soft’ limitations often hinder its capabilities. For example, a computer’s RAM and processing power can often slow Excel and sometime grind it to a halt.
Technological advances and the digital economy has led data to be considered the oil of the 21st century. In fact, it is expected that the digital universe will reach 44 zettabytes in 2020. Although for an organization, the amount of data in a database is a microcosm of the digital universe, understanding whether a database is expected to grow at a rate that will quickly surpass Excel’s capability is very important.
In many older excel versions, the practice of sharing or collaborating in Excel has been challenging at times and even unproductive. The issue has been mainly to do with work being saved on top of someone else’s, read-only capability when others are accessing the file among other issues. In the new Microsoft 365 version, Shared Workbooks moved to Co-Authoring with new capabilities that smooths out some of the issues that users have faced in the past.
Excel offers a variety of protections and securities in its stack, ranging from File to Worksheet encryption, accessibility, digital signature and more. At the same time, most of the security functions are not viewed as a security by design, meaning that cybersecurity concerns are elevated and someone may still be able to break through it. It is always important to consider whether excel-based database is the right option for the type of data and organizational need.
Although excel is able to manage multiple datasets with relatively intricate workflows, it is important to note that the larger the dataset and its complexity, the further one is into uncharted territory where the possibility of human-error, Excel breaking or slowing down increases exponentially. At this time, it is important to consider moving into a dedicated database or at least ensuring that a few backups are available.
Strong data management will often encompass governance, integration capabilities, architecture, data quality, standards and more. This leads to improved consistency, accessibility and, trustworthiness in a timely fashion. All these areas can be challenging to maintain in an Excel database.
There are many cases where inspecting changes made to an Excel file can often be limited since it does not offer the level of granularity that may be needed. This is less of an issue in the Microsoft 365 since the Security & Compliance, when configured properly, can help create an audit trail to support organizational auditing.
Excel remains a powerful tool used in many organizations around the world. Understanding its strengths and weaknesses can help an individual or an entity determine whether to use excel as a database, a provisional one or whether to move into a dedicated solution. For those interested in moving to a dedicated database management solution, some of the most popular ones are: SAP HANA, MS SQL Server, Oracle, MS Access, MongoDB and IBM DB2.