Microsoft Excel is not a Database

Microsoft Excel is a spreadsheet application like Google Sheets and Lotus 123; MS Excel is not a database program.  While there are similarities between a database and a spreadsheet, they are not the same thing.  Spreadsheets and databases have two distinctly different uses and two entirely different infrastructures, objects, methods, properties, and coding.

The main difference between an Access database and an Excel workbook is how the data is stored, organized, manipulated, and ultimately visualized.  Working in a spreadsheet is nothing like working in a database. Most people do not do their charting, or analysis in a database, they do that in Microsoft Excel.  The best way to use databases is to house the data, and then send the data sets to Excel for data visualization.

 

Think of Excel workbooks as frontends to your Microsoft database backends.

 

 

 

 

What is a Relational Database

A relational database is a type of database that organizes data into tables, based on relationships between the tables.  The tables are similar in structure to an Excel table, comprised of rows and columns.

Microsoft has four relational databases:  Access, SQL Server, Azure and Power BI.

In a relational database, you establish relationships between the tables, defined by primary keys and foreign keys. The queries are based on those relationships.   That is why they call it a “relational” database, there are relationships between the tables and queries. By using primary keys to establish relationships, tables can join to perform complex queries that ultimately create data sets. This is how a database works, and this is one of the reasons Excel is not considered a database.

Excel does not do this.  Instead, Excel uses Lookup functions to “connect” various tables, and to retrieve values from other tables. The Excel XLookup for example looks at one table and based on criteria, returns a dataset, from another table, based on finding a “matching” record.  Here the criteria field is the primary key, in a roundabout way.

 

 

Where are Relational Databases used?

Relational databases are used in all sorts of organizations, from business to non-profit.  Databases store and manipulate large amounts of data as the backbone of their business information systems.   Most organizations have some sort of databases, yet they still live in Microsoft Excel.

Interesting note: Databases are usually the data-source for all Excel data in the business setting. Source data is stored and maintained in databases.  Users are almost always kept away from the source data files and forced to work in Excel files.

Examples of popular relational databases include Microsoft Access, Microsoft SQL Server, Microsoft Azure, Microsoft Power BI, MySQL, and Oracle.

 

 

Microsoft Excel is a Spreadsheet (Workbook)

MS Excel, Lotus 123, and Google Sheets are spreadsheets, aka workbooks. Typically, users use spreadsheets at the desktop level, and for the most part, manually manipulate small amounts of data. Excel workbooks usually require a lot of user intervention.  Examples would be entering data, copying, pasting, changing formulas, etc. In comparison, a database typically updates on its own via code.

Spreadsheets are easy to create, to change and just as easy to break.  Databases usually require an experienced programmer to make changes to the structure of the file.  Database access is usually restricted to the IT DBA.

When you want to compare a database to an Excel file, think of a spreadsheet as the Wild West of applications.  It is an open application where you can paste pictures, type words, enter numbers, paste shapes, anything.  In Excel you can create a useful or useless document. No holds barred, do what you want.  Who knows, the numbers might even tie out.

 

 

 

So why use Excel as a database replacement?  Databases have a lot more structure than spreadsheets, and as a result, databases take more work to develop than do Excel workbooks. And that is why so many companies try to use Excel as a database. Excel costs less to program.  Your office staff may not know Access or SQL Server, but most likely they are fluent in Excel, so there you go.

Microsoft Access, SQL Server, Azure, and Power BI are relational databases.  All of the Microsoft databases can integrate with Microsoft Excel, as the backend. This way, you get the best of both worlds, fully integrated and automated Microsoft solution, a Smarter Microsoft Solution.

 

 

 

How Business Uses Microsoft Excel

Business users perform various tasks on the information in the file, such as creating financial reports, analyzing business trends, drilling down with KPI Dashboards, Pivot Charts, etc.  Excel allows the user to derive valuable business insights and make well informed business decisions that impact the company.

Business, finance, education, use workbooks such as Excel and Google Sheets to manage and analyze data efficiently. Excel Dashboards are the best way to visualize your data.

Databases are used to store said data. Below is an example of one of the Excel Dashboard we programmed for a business client, where the data came from an Access database.

 

 

 

 

Spreadsheets are cheaper to set up than are databases

The amount of work required to program each file is the main difference between developing a database and programming a spreadsheet. Setting up an Access or SQL Server database can take hundreds to thousands of hours.  Setting up an Excel file can happen in minutes. Hence the number of people that use Excel as a database.

 

Excel files can be created for a fraction of the cost of a Microsoft Database. That is why so many companies use them, and why so many people work in Excel daily.  Cost is a deciding factor.

 

While Excel is not a true relational database, a spreadsheet can ultimately do much of the work that an Access or SQL Server database does.  If set up right, Excel can get many of the same end results as a database. It just takes a lot more effort to do so.

Trying to do this in Excel will require a lot of advanced VBA. Performing this task in Excel requires a lot of staff hours, as the user needs to do much of the work manually.  A database can do in minutes what a user can do in days or weeks.

 

Interesting to Note:  Excel is cheaper to program but more costly to use.

 

 

 

 

---

 

There are many differences between spreadsheets and databases

• Excel files tend to be 15 or fewer megabytes in size.
  • Anything more and performance suffers.
• Access databases can reach sizes of 2 gigabytes.
  • Access databases allow you to avoid using SQL Server.
    • For small, self-managed databases.
  • SQL Server databases can be hundreds of gigabytes in size.
    • SQL Server databases are maintained by a DBA.
      • Optimized for performance.
    • Databases are typically stored on large powerful servers.
    • Excel files are often stored on the desktop or C drive, though a network is recommended.
    • In a database you use Queries and calculations and code (VBA, SQL, etc.).
    • In Excel you use Functions and code (VBA, SQL, etc.)

 

Too Small to be a database: Databases are usually measured in gigabytes, whereas Excel files are measured in megabytes.

 

 

Queries versus Excel’s Dynamic Array Functions

In a database, Queries are used; in Excel, Functions are used.

That said, Excel is becoming more database-like.  Even though many of the new Excel Spill Array Functions work much like Access Select queries, they are still Excel formulas.

For example, the new VStack Excel Array Function works much like an Access Union Query.  These new functions make Excel more database-like, and thus easier to use and easier to program. This appears to be the new direction for Microsoft Excel development.

 

 

---

 

 

Instead of trying to use Excel as a Database:

Integrate Excel Spreadsheet w/ Microsoft Database

Microsoft Excel is not a database, Excel often integrates with Microsoft databases such as Access, SQL Server, Azure and Oracle.  An Excel front-end with an Access back-end is one of the most common programming solutions we provide. These are huge money/time savers.

Integrating Excel with Access allows you to leverage the data storage and calculation abilities of a network database, while not requiring as much database programming.

Many companies that do not want to invest in a database, but the ones that need additional storage and performance abilities will use Access as a backend to their Excel files, and that seems to do the trick.  While Excel is not a database, it can work closely with one, providing much of the benefit, but at a fraction of the cost and effort. Most bang for your buck, best of both worlds.

 

 

 

Conclusion – Why Excel is Not a Database:

Microsoft Excel is an amazingly powerful and flexible tool that businesses use daily.  Excel has so many different capabilities.  Excel can do much of what a Microsoft database does, but it is not a database, it is a spreadsheet.

As we have been saying, Microsoft Excel is not a database, but many business users often try to use it as an alternative to a database.  Typically, the motivation to use Excel over Microsoft Access or Microsoft SQL Server usually comes down to the cost of programming the database.  Database programming can be expensive.  Then there is the cost of the database software as many companies pay an annual fee for their licensed version of SQL Server.  Excel is basically free in comparison, as is Microsoft Access, as all that you pay for is the monthly 365 license fee.

Excel is a spreadsheet application that allows the user to store and to manipulate data in a tabular format.  While excel it is not as advanced as a relational database application, the user can use Excel at the desktop level, to perform basic tasks such as crunching numbers, financial reporting, KPI Performance Dashboards, and of course, manipulating data.

However, Excel has many limitations as compared to a relational database.   Excel files that exceed just 10-15 megabytes in size, will quickly run into performance issues.  You might wait 10 minutes for the file to open, you might wait 30 minutes for it to calculate, so size in Excel is an issue.  Not much of a database if you are restricted to 15 megabytes in size.

 

 

---

 

Need Help with your Excel Workbooks or Microsoft Database Applications?

 

 

 

If you would like to know more about Excel, how it works, how to set up your files, or if you want to hire our team of Excel and Access experts to build a custom solution for your organization, give us a call.