Spreadsheets (Excel) compared to Database

Triangle of Solution

Based on number of times used:

1. 1-5 use a piece of paper
2. 5-20 use a spreadsheet
3. 20+ use a database

 Spreadsheets Disadvantages

1. Data should be stored once and only once. Using spreadsheets often creates multiple out of date files
2. VLookup is far inferior to SQL links
3. Some problems are very difficult to solve in comparison with similar solution in database transact sql
4. Limited or no programmatic power dependent on developer
5. Out of systems – suggests refactor not found
6. Out of security control
7. Unable to unit test
8. Unstructured data
9. A little knowledge is a dangerous thing
10. Spreadsheets exported from a database – just adds another step – NOT combine, reduce, eliminate

 Spreadsheet Advantages

1. More people can prepare them
2. Users can demonstrate requirements by attempting to build what they want . Form of tracer bullet
3. Possibly more print control
   1. offset by users knowledge of how to use Context Menu in grids
   2. why are they printing anyway – why not two screens – are they avoiding IT – find it easier to work with paper

Spreadsheets Interesting

1. For users showing advanced spreadsheet skills. Could promote move to SQL Express of other. Or MS Access using ODBC. Or Reporting.
2. Could direct them to request views for plugging in

Real-life Examples

1. Model in Database exported to Spreadsheet
   • Created a model.
   • User downloaded to spreadsheet and then edits some data.
   • Model is blind to changes.
   • Also Model then changed in database and spreadsheet is out of date.
   • Also spreadsheet is not used for a few days so data in it becomes a little out of date.
2. User wants to export to excel – to print out a stock driven demand. This is a step that was removed before in order to save time.