The most commonly used database application in the world is not a database application, but a spreadsheet. Spreadsheets are great and what attracts us to them is mostly their simplicity and flexibility in storing whatever data we have and performing functionality we need. Spreadsheets also have fundamental limitations when it comes to managing data. Those limitations can result in errors, inefficiency and security vulnerabilities making Excel (and other spreadsheets) the “World’s Most Dangerous Application.” Some of those limitations can be overcome with some programming, but that can be tedious and subject to reliability. This post will discuss the benefits and limitations of using a spreadsheet instead of a database application.
Spreadsheets may be fine for small data sets. However, spreadsheets become inefficient once a data set starts increasing in size. For example, it can become difficult to see data beyond certain limits requiring a user to scroll vertically and horizontally to reach a certain value. It can also become difficult retrieving a set of rows that meet certain criteria.
The scalability limitation of spreadsheets is most evident when working with multiple spreadsheets or tables that need to be linked and related. Due to the two dimensional nature of a spreadsheet, setting table relations is very difficult or impractical. Relating tables is fundamental to a viable database, which helps the retrieval of data. For example, if you have one spreadsheet for customers and one for orders, attempting to retrieve customers in a given city who ordered a specific item can be achieved relatively easily with a relational database application, but quite difficult with a spreadsheet. Now imagine relating many more tables (e.g. employees, payment method), then trying to retrieve the related data using a spreadsheet is completely impractical.
Data is as good as its accuracy. Typically, in a spreadsheet a user can enter data in cells without validation, whether it’s due to an entry error or just choosing to type whatever the user decides. For example, entering a word (e.g. “hold”) in a column where the other entries are dates becomes problematic in many aspects such as searching, sorting and reporting. The problem of data integrity and entry errors is eliminated in database applications because an entry is validated against a specified column type.
Data Redundancy and Integrity
There are no checks for data integrity in spreadsheets. For example, if you have Customers on one sheet and Orders on another sheet, then it’s possible for a user to enter an order for a customer that does not exist. Also, it’s possible to enter the same customer multiple times on the Customers sheet without realizing that they are duplicates.
One possible solution in a spreadsheet application is to combine the customers and orders data on one spreadsheet (a flat file), but that will require the user to redundantly repeat a customer’s information magnifying the problem of redundancy and efficiency. In this case, searching also becomes a problem. For example, a user may experience difficulty trying to find all orders of a certain customer if the customer’s name is not consistent in all rows.
Databases are beneficial because they help maintain data integrity, eliminate redundancies and unnecessary data duplication.
Cloud stored spreadsheets can be shared and will allow multiple users to view and edit at the same time. However, there are no restrictions on which files, sheets or columns can be viewed or edited by certain users, making security and accountability a high risk.
Most database applications have user groups for which permissions can be given. Database applications will generally track changes made on user and/or record basis. Database applications make collaborating much easier allowing users to work simultaneously on the same record.
One of the key reasons for using computers is automation. Automation can take many forms, such as computations, workflow, updating related records or sending notifications via email when an event is triggered.
Spreadsheets excel in computations, but they still have limitations. There can be a loss of consistency across formulas due to a change in a formula that was not copied to new cells.
In a database application, the formulas are centralized. One formula can be made to apply to all rows (exceptions can be made) without the worry that the formulas may not be the same across all rows or if a user arbitrarily changed those formulas.
Automation is key to managing workflow and steps in a process. Database applications are quite flexible when it comes to setting up workflow, whereas spreadsheets are totally lacking in the ability to implement a workflow.
The need for automation is necessary when actions are required to take place on a recurring basis. A database application can be setup to a specific schedule to trigger predetermined actions on selected records.
Sharing and Publishing
There is no feasible way to publish data from a spreadsheet to a website, at least not without manual and frequent updates. Records in a database application are reusable. Whether you want to publish the records on a website, allow limited access for your customers or use it on a mobile app.
Trying to create reports from a spreadsheet can be difficult and may produce inaccurate results. Database applications have a variety of reporting tools that allows the end user to obtain their reports with a single click.
In conclusion, there are times when a spreadsheet application will work fine for storing data. This is mostly for small data sets, consisting mainly of just a few sheets and without much need for security, relating to other records, collaborating, sharing or publishing.
Luckily, there are many database applications that can import your spreadsheet and have you up and running in little time.
When migrating from a spreadsheet to a database application, some of the methods you have used in organizing your data may need to adjust. For example, you may need to divide a single spreadsheet into multiple tables, since the database application will be able to efficiently handle relations between various tables for you. Another example is moving inactive clients to another sheet; you do not have to move them because the database application will track your active and inactive clients for you.
It’s no secret that creating a database application is a very complex process. At the heart of which is difficult syntax in selecting and joining tables as well as tedious programming languages, however the benefits are substantial.
TO EXCEL OR NOT TO EXCEL?
Dangers and Limitations of Using Spreadsheets
© CMAS 2016