Can Excel serve as a Database?

Excel offers large Worksheets. Data can be arranged and stored flexibly, searched and acted upon. Do we have a Database here? * Let’s sort out Excel as a Database.

What is a Database?

Simply speaking, a database is any structure in which data is to be arranged along with tools to store, manipulate and retrieve the data.

Several such pre-configured “data arrangements” are available as products, each developed in its time to solve data-related challenges. Let’s mention a couple.

Relational Database: In a relational database data is arranged in tables with relations between them. Using the SQL language, one can create the database tables, insert data into them and retrieve data, among other things. Relational Databases Management Systems (RDBMS) have grown to be very mature products, serving the data requirements of almost all of the information systems in the world. For more details about Relational Databases and SQL – read my Blog Post: Understanding Relational Databases: The Basics.

Graph Database: In Graph Database, first introduced in 2006, data is arranged in a Spaghetti-like scheme, in which entities (nodes) are connected between them by meaningful relationships (edges). For example, a customer can be connected to a Sales Order in a “placed” relationship, in essence telling us that the customer has placed this order. Graph Databases are ideal for related entities with frequent changes, such as describing Facebook’s map of friends.

Key-Value Databases: The basic structure here is that of pairs of key-value, for example: a phone directory listing can be thought of as a list of pairs where the key is the person’s name and the value is his phone number. The value can be a more complex structure, such as a document or an array of attributes.

Excel as a Database

Taking the above definition of a Database, you can clearly see how some useful data arrangements can be found in a Worksheet (or several Worksheets) to serve as a Database.

In fact, the magic and power of Excel stems from its versatility of functions. It can serve as a Database, a real-time calculation grid, a visualization board, a platform for developing computer programs and basically as a client, or user interface, in information systems.

Create a table in Excel to store your customers – and you have a Database.

Add another table with a Vlookup to your customers table – and you have a more sophisticated Database: linked tables.

Where Excel Comes Short as a Database

As you recall, in my definition of a Database I mentioned that each Database product was developed to solve some data-related challenge(s).

Let’s see some of the challenges for which Excel falls short as a Database:

• A continuous table of 5 Million rows cannot be stored in Excel. Each Worksheet has a limit on the number of rows made available.
• The typical grid of cells is nothing more, nothing less than a grid of cells. There is no design to optimize for any data type or relationships between the cells. Nor there are built-in methods for efficient storing, manipulating and retrieving the data towards any concrete use-case. Try to ask a question that needs to gather and connect data from 4 tables and you may come to hate Excel in 10 minutes…
• Try to lookup data in an 800,000 rows table. Try to connect that table to another table with a formula. Add some formatting to the cells. How easy and fast can you add rows, delete rows and even find data? Even when using some of Excel’s built-in functions (such as finding a value) – you may wait a significant amount of time. And that’s for a simple structure of two tables and a single column with formulas…
• Excel is not designed to serve multiple users at the same time. Even if you dedicate an Excel Workbook to serve as a Database for other Excel files hitting that Database – you cannot have two users working on two devices opening up this Workbook concurrently.
• Data Integrity. If you have several related tables scattered around as your Database, you risk breaking up some of these relationships when updating/deleting rows. Excel is not designed to secure the integrity of the data across all tables for you.

As you can see, some of the most needed traits of a Database serving a robust, scalable information system, are missing in Excel.

This is why for most business applications Excel wouldn’t be considered as a viable Database.

What Can We Do When Excel is not a Viable Database for our Application?

Well, we can choose a suitable Database.

As a relational Database is the suitable choice for most business applications, there are plenty of great RDBMS products to choose from, some are completely free.

Here is a typical architecture I employed for one of my customers:

Excel can serve as a great user interface communicating with a central Database. We enjoy the benefits of a robust, scalable relational Database along-side Excel built-in functionality, visualization features and programming flexibility.

In my Blog post here in this link I demonstrate how to connect your Excel VBA program with an external Database for implementing mission-critical business applications where Excel serves as the Client and the popular MySQL Database serves as the Server. In part two, I go further and show you actual SQL statements being communicated from Excel VBA to MySQL Server.

If you want to learn how this is done right, even if you know nothing about relational Databases, you may want to check-out my course: Business Applications with Excel and MySql Database.