HomeServicesTechnologyAnalyticsOur ClientsAbout PCAContact Us

Is MS Excel Really A Database?

The short answer: Yes, to a point.   Excel can be used as a "database" to store and manage business data, provided your needs do not exceed limitations in the areas data integrity, security and multi-user support.   MS Excel allows you to manage data in two dimensions (Rows and Columns), and provides database-like functions like hlookup and vlookup, sorting and filtering (and the like), that enable some level of reference and re-use of data elements.   And with enough custom VB code, you can get your Excel spreadsheet to look and function like a real business application!

Excel -- Managing Vs. Storing Data

One of biggest drawbacks of using Excel as a database: the data model that is necessary to optimize how your data is STORED – ideally, a single instance of every field element with no duplication – is often at odds with the data model that is ideal to MANAGE your data.  It is very difficult for example to reference existing data elements inside Excel to insure a perfectly normalized set of data, in the same way that a relational database is designed to enable.
Because of the difference between a data model that is optimized for storage and the data model that is suitable for managing edits, adds, and deletes, Excel developers often end up creating two (or more) instances of the same data element.  And whenever data is managed in a "denormalized" state (duplication of the same data element), it is only a matter of time before the data becomes fragmented and very challenging to maintain.  This is not to say that it can't be done, but the time & effort required to maintain an acceptable level of data integrity can become quite expensive!  Attempting to produce reliable reports from a denormalized Excel spreadsheet is usually when this effort becomes most apparent.

Excel Multi-User Limitations

Another major limitation of using Excel as a database is the inability to support multiple, concurrent End Users.  Popular work-arounds to multi-user limitations include: putting the spreadsheet on a shared network drive, and, for Internet access, exposing this shared drive to the Internet with a remote access program like Citrix or GoToMeeting.  What neither these approaches will address however is locking out fields when more that one User is attempting to update information, and the resulting train wreck that occurs.  Common consequences of using MS Excel in a multi-user environment include corrupt data lost data, and/or Excel crashing.
Excel is usually adequate to support 2 or 3 people involved with managing the data.  This is most frequently accomplished by emailing around the spreadsheet, and keeping track of "who's got the ball."  Any more than 2 or 3 Users however, and you're going to be spending a lot of time cutting-and-pasting data from different sources, and always wondering if you have the latest, most up-to-data and accurate set of data.

Excel Security Limitations

Excel supports password protection for the entire workbook, for individual worksheets (tabs), and even protecting individual fields from being changed by Users.  The security methods that Excel provides are usually adequate for small groups E.g. 2-5 Users.  If you need to support larger numbers of Users, you're much better off using a real database application like MS Access, or even SQL Server for more robust security management controls like role-based security, and/or integrating the database with network-wide security mechanism like Active Directory.

PCA Recommendation for moving Excel data to a Database

No matter how much time and expertise you invest in MS Excel, a spreadsheet that is designed to support a small handful of End Users will never yield the levels of data integrity, scalability, ease-of-use or security that are the hallmarks of true database platforms like Oracle and SQL Server.  Excel is a great tool to get large amounts of data into a database from another application. As an alternative to building a direct interface via ODBC or API, Excel's CSV format is a very common method to share data among different applications.   Because Excel is so flexible and easy-to-use, with powerful built-in charting functions, Excel provides a great tool to display different views of business data, but falls short of providing many capabilities that tend to become important over time, especially within distributed multi-user environments.