Excel is not a database

Archived Topics
1

I have been having some trouble convincing some colleagues of the difference between a real databse and a 'spreadsheet". I have found the following article…administrativearts.com/2009/11/1 … -database/

I was hoping to pool from this forum and the IA guys a better answer.

2

My effort at a less technical response…

Spreadsheets are only suitable for short-term, single-purpose data. They’re useful for helping one person to make some calculations, produce a chart for a presentation or report, etc.

If any of the following statements are true, it is time to use a database:

  1. The data is used by more than one person.
  2. The data will be used and updated for more than a few days.
  3. You are using a macro for any reason.
  4. The data does not make sense in a single table, or you have multiple sheets which contain related data. You should not keep having to switch between sheets to ‘look up’ data.
  5. The data is important and cannot be recreated with less than one hour of effort.
3

I tend to fall into the camp who champions the idea of databases over spreadsheets, particularly for collaborative applications. However, Excel is an awesome tool that I use frequently. I really like the column filters and expression language (which provided inspiration for Ignition expressions). The simple graphing is pretty slick too. I’m not so strict in my criteria as Simon, but appreciate the spirit. I’ve seen many cases of abusing spreadsheets where a database would be more appropriate. Not a single case comes to mind of someone using a database where a spreadsheet would have been a better tool.

Here are some rough criteria that indicate for me to use a database: when relational “lookups” or aggregate queries are required, conducting concurrent edits especially from different locations, dealing with large datasets, heavy macro use, or any time another application is doing anything to the data. “Large datasets” often refers to 1000 rows, possibly less. Not that Excel can’t handle it, but anything more complicated than finding a single row (like a phone list) becomes unwieldy and starts getting into query territory - databases not spreadsheets.

My personal $.02.

4

From a technical standpoint:

I believe from memory you can run SQL UPDATE, INSERT etc queries against excel… DELETE doesn’t work though.

I reckon the biggest reason for not using excel as a database is the inability to maintain a unique key.

Experience from developing an application that used excel as a backend (historical reasons :slight_smile:… Ended up having to move to MS SQLserver because of the problems with delete and keying as mentioned above.

5

Um… Excel is not ACID compliant… enough said. If you care about your data (Obviously you do), then why take the risk?