- spreadsheets are almost always likely to be stronger at number crunching, complex formulae and the (selective) application of numerical rules to data; although the differences between the two tools are lessening – most databases do have the ability to change records according to arithmetical formulae
- spreadsheets are usually likely to be better for sophisticated graphing and charting; again, most databases have (elementary) visual representation of data, but that’s usually easier to achieve, manipulate and save the procedure for in a spreadsheet
- spreadsheets’ pivot and goal-seeking functionality for ‘what if?’s is often better than the equivalent functionality in databases: their grid layout is ideal for watching the knock on effect across a wide range of data when one portion of it is changedâ€¦ the amount needed to reduce a debt by even variable payments over 12, 18 and 24 months, for example
- when they can be restricted to working with (relatively simple) lists, spreadsheets, are generally more useful for their visual ‘all-in-one’/’all (or almost all!)-on-the-page’ display; again, the grid format sees to this
- spreadsheets are likely to be quicker to set up and may be easier to use. There’s still nothing like a wizard-based input to guide users through which data to put where in a database, as opposed to tabbing around, though
- spreadsheets often easier to replicate data, e.g. CMND-D to populate successive cells down when the data is the same.
Otherwise, databases have the overall edge for:
- reports; report generation submodules can be saved for repeated use (see 7). What’s more the scope and variety of “canned” and customizable reports is likely to be greater with a database than a spreadsheet
- the quantity of data and capacity to grow as needs dictate; databases are designed to open quickly, for example, and refresh even with tens (or hundreds, or more) of thousands of items of data
- portability of data into other formats, XML especially; anything can usually become anything else (although with a longer ‘trip’) in export forms from a database; similarly, the partial export (or certain, flexible and easily settable ranges of) data is easy with a database
- visual formatting beyond cell colors and fonts is greatly enhanced in most database systems
- byte for data ratio – relational database tables do not duplicate when designed properly; so no redundancy. Speedier: data for customers (addresses, emails, credit card numbers etc) needs only to be entered once for each customer, no matter how many purchases they make from you
- related spreadsheets are less simple than relational tables in a database: there are relational spreadsheets; but databases are designed to work that way
- databases are often scriptable (see 1), in which case they’re likely to be more flexible; again, databases are designed to allow repetitive, storable and flexible sets of procedures to be applied to extremely granularly defined (portions of) data
- security: level and granularity of locking (often down to the column or row; or even the field) is usually greater in the case of databases than it is with spreadsheets. FileMaker Pro, for example, has a whole security/access control system of different levels of rights; can reduce mistakesâ€¦ (inexperienced) users’ areas restricted etc.
- overall fitness for purpose. Databases are intended to store (large quantities of) data reliably and are optimized so to do.
So, take a careful and critical look at your requirements, decide which criteria are important and jump accordingly. These sites may take you further: