Channel Mike

Most individuals and small businesses use spreadsheets to manage their data. Spreadsheets are amazing tools, but have some limitations that should cause you to consider databases.

The spreadsheet was invented in 1968 but wasn't readily available until 1978 with the introduction of Visicalc. You can read Wikipedia's interesting history of spreadsheets if you're interested. Today, Microsoft Excel reigns supreme, but other options are Corel's Quattro Pro and the free options, Google spreadsheets and Open Office/Star Office. There are about a half-dozen other off brand options that I won't consider here. You can see these options at Wikipedia's comparison of office suites.

The pricing of these databases can get confusing. Excel will cost you $230.00 for a full license on Windows or $200 on the Mac. However, other options are to buy an upgrade for around $100 or to buy a noncommercial license, meaning home use only, for $120. All of this being said, it's far more common and reasonable to acquire Excel as a part of an Office suite that includes a word processor and other tools. The Office 2007 suite is currently priced from $150 for the noncommercial Home and Student edition to nearly $700 for Office 2007 Ultimate. Each version offers a different set of tools. Quattro Pro had been the only real competitor to Excel for nearly 20 years. It is now only available as a part of a suite from Corel. The entire suite costs from $100 to $350 depending on the accompanying programs. The Open Source movement has provided its own office suites. Openoffice is the most popular and probably the most stable and powerful. It is free, of course, and offers Calc as its spreadsheet along with competitors for other office tools. Google is offering a free online spreadsheet as well.

If you just need to create a list of numbers and sum them or do some simple analysis, any of these tools will do fine. There's no point spending any money on a commercial variant. But if you're doing more, like trying to make your spreadsheet manage your company's data because you're too cheap to buy a database tool, you'll want to look a little deeper. Spreadsheets have a limitation on how many rows (or records) you're able to store. They usually have a limitation on the size of the file as well. If you need to share files with associates, you'll want to use the same software, or make sure you're saving to a file type you can both read. Different spreadsheets have different capabilities when it comes to importing and exporting data to other formats and other tools. If you want to stretch the spreadsheet to its limit, you'll want to be able to write macros or scripts to automate its functionality. You may want to write your own functions as well. All spreadsheets will add numbers, but not all will meet the needs of everyone.

If you're storing a lot of data, you need to take into account the limits of the spreadsheet's storage. Google's online spreadsheet is limited to 256 columns and 10,000 rows, to a maximum of 100,000 cells. They will also only hold 500k of data (not counting embedded images). In contrast, Microsoft Excel 2007 allows over 18,000 columns and over a million rows. Most of Excel's limitations are in fact dictated by the disk and memory on the PC running it rather than on its own limitations. Excel has just pushed to these limits for the 2007 edition of Excel to match the identical row and column limitations of Quattro Pro. Openoffice and its equivalent Staroffice only offer 256 columns by 32000 rows, and they actually bog down the program by the time you hit 100 by 100,000. If you need your spreadsheet to handle large amounts of data, you really must choose Excel or Quattro Pro.

I'll admit to never having programmed behind Quattro Pro, so my comments here are passed along from web searches. I have done extensive programming behind Excel, from simply recording macros to writing my own functions to import/export between Access databases to even automating third party applications via buttons in Excel then manipulating the data and filling Excel sheets with it. I can vouch for Excel when I say that its VBA scripting language is both flexible and powerful. The same language, VBA (Visual Basic for Applications), is used by all Microsoft Office products and is licensed to other software vendors as well. This breadth of use resulted in an Amazon search bringing up over 3,000 books that mention VBA. A brief perusal of the list brought up book after book that are solely on VBA. The knowledge of VBA is definitely worth having as it will carry over to other software products and is supported by Microsoft. Quattro Pro uses PerfectScript. I could only find a few comments on it while searching online and found only about a half-dozen books on Amazon that mention it. All of these books were on Wordperfect, indicating that Perfectscript would be a chapter or two, not the whole book. I haven't gone into support as an issue, but it was difficult to even find the knowledge base for Quattro Pro. It is treated as a part of the Wordperfect suite, which it is, but not as a standalone product. Microsoft's Excel is all through MSDN and has been the standard spreadsheet for so long that you'll never have trouble finding someone else who has solved the same problem you are having. If you want to push the limits of your spreadsheet, I have to recommend Excel.

Spreadsheets were not invented to be databases, but since their beginning were used as such. Modern databases, such as Excel, have incorporated features like increased capacity, data sorting features, import/export tools, and pivot tables to make managing databases easier. This has led in the encroachment of spreadsheets into what was traditionally the database market. You can realistically manage mid-sized databases in a spreadsheet nowadays. That is not to say that it is either wise or efficient to do so, however. Read my advice on managing your data to find out more about deciding between spreadsheets and databases.