4.2.5 When to switch to a database?

There are two important factors determining when a list in Excel should be implemented by a database.

The first factor is efficiency. Although Excel is fairly efficient with smaller tables, it becomes inefficient with larger tables. The reason is simple: a spreadsheet is not intended to handle the sorting and filtering of large tables. Furthermore, a spreadsheet loads everything into memory. This means as a table grows larger, the required amount of memory increases proportionally.

The second factor is flexibility. While Excel lists offer some flexibilities, it still lacks some fundamental features that are standard in a database. For example, the criteria of list filters cannot be computed values. This means you cannot say ``compute the total of sales and display the item only if the amount of more than 50''. You can get around the problem by adding a new column for ``total of sales'', but it is merely a patch that does not work well in general. A database query, on the other hand, can be quite general, and criteria can include computed values based on fields of a row.

Yet another flexibility that is lacking in a List filter is the ability to relate one table to another table. In our example, we may have another table storing ``register'', ``start time'', ``end time'' and ``employee ID''. This table tells us whom worked at which register at what time. If we want to combine the two tables to tell us ``who sold the most amount of tomatos'', we need to ``relate'' the two tables by register and time.

Excel (or any spreadsheet program) are not designed to do this. There is a way to do this in Excel, but is quite involved (using ``vlookup'', ``address'' and ``indirect''). One has to substantially alter a table to prepare for this kind of filter. However, a relational database can handle this with a single query.