Why You Should Use Excel As A Database

Posted on August 10, 2021

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

One reason – because that is already what you are doing. You have invested a lot of time into your spreadsheet, folks are familiar with it, and maybe it has lots of advanced logic in the form of macros and VB that cannot be replicated outside of Excel.

Is it ideal? No, and one quick google search will uncover a few dozen compelling reasons not to. But what if you could continue to use the spreadsheets just like you are currently doing, but the data automatically gets deposited into a central, commercial grade relational database?

Before I dive into how this is now not just possible, but easy to accomplish, let’s start with some background…

We already know the enormous value spreadsheets offer us, and I don’t think it is an exaggeration to say that a significant portion of the world’s GDP is supported by Excel. So, if we can improve their utility, it stands to reason that there is significant untapped value that still remains to be exploited.

One major area that is underleveraged is data management and analytics, primarily because individual users of a template “master” spreadsheet often save instances locally as flat files. This makes it difficult or impossible to access data contained in users’ saved spreadsheets in an efficient manner, and this in turn makes data analysis and mining pretty much out of the question.

For example, if a team of salespeople use a template spreadsheet to generate quotes or proposals, and then save an instance of the spreadsheet locally every time they create a new quote, then it is extremely difficult to report on overall performance of the sales team or to generate a snapshot of the latest figures – until or unless all of these individual saved spreadsheets are combined into a single spreadsheet. If further slicing and dicing or querying of the data is needed to conduct more advanced analytics, for example to look at trends, dependencies or relationships between parameters, then Excel really begins to reveal its limitations and generally becomes entirely impractical to use for these purposes.

So, this using Excel “as a database” clearly limits the leveraging of data. Relational databases on the other hand are ideally suited for this purpose. They provide a central data repository in a form that lends itself to data analytics (as well as transfer of data to other software systems). So, if there was a way to blend the ease of use and familiarity of Excel with full database functionality, then a far more powerful system is created.

EASA software, long the leader in low-code development for converting Excel into web apps, has now introduced a new solution, EASA “Sheets”. This new tool automatically adds full database functionality to Excel spreadsheets. With no knowledge of databases or any programming skills, a “citizen developer” is able to share important Excel spreadsheets to multiple end users across an enterprise, while ensuring that the data within every instance of the spreadsheet generated by every user – inputs and calculated outputs – is captured into a relational database server on your corporate network or cloud. EASA is delivered with a commercial database (MySQL) so it is not necessary for customers to already possess an RDBMS (relational database management system) – though EASA can be configured to use other database types.

As the data is now centralized and in a database, operations that were either very difficult or impossible are now easily done. This includes aggregation of data contained in multiple workbooks, complex multi parameter filtering and data analytics, as well as enabling easy integration to dashboard software such as Power BI and Tableau.

In addition to the database functionality, EASA Sheets provides a platform that manages spreadsheets such that common complaints such as version confusion, uncontrolled access and copy proliferation (along with the security risks associated with having uncontrolled copies floating around an enterprise) are eliminated. To learn more about the full set of capabilities and how it works you can go to https://easasoftware.com/excel-spreadsheet-management/ or watch a video at https://youtu.be/RB5So7mJwUM.

So EASA offers you the unique ability to continue to use your Excel spreadsheets as you do today, as a database if that is the case, but transparent to the users, actually does transfer key data into the database allowing you to tap into all the advantages that using a database instead of Excel would provide. It might not be exactly having your cake and eating it too, but it comes pretty darn close.