Also from Plum Solutions via Patrick, a list of financial modelling and Excel resource links. They include Pointy Haired Dilbert: "probably one of the most relevant and useful blogs I have come across for the heavy-duty Excel user. If you subscribe, barely a day goes by without a fascinating tip or Excel tidbit."
Back to the links Patrick sent me. In Five Excel Formulas to make you cringe, Plum Solutions list five practices that they regard as bad. These are: explicit addition or addition and division instead of SUM and AVERAGE, too-long formulae, too many nested IFs, and wiring constants into formulae. Some if not all of these demonstrate defects in Excel as a programming language. For example, nested IFs and long formulae would be much easier to understand if Excel would allow formulae to be written over multiple lines, so that one can indent subexpressions. Formulae would be easier to shorten if Excel made it easier to define functions and didn't force one to drop into VBA. This was the point of a prototype I built that tried to avoid the need to do so: Defining Excel Functions without Visual Basic: a compiler that converts Excel function definition sheets to VBA.
In the spirit of my previous posting, I shall again mention my Excelsior spreadsheet generator to point up difficulties one has with Excel: this time, in documenting a spreadsheet. In Gliders, Hasslers, and the Toadsucker: Writing and Explaining a Structured Excel Life Game and How to Document a Spreadsheet: an Exercise with Cash-Flow and Loans, I show how I can write high-quality spreadsheet documentation as part of an Excelsior program. And in Documenting Spreadsheets with Pseudo-Code: an Exercise with Cash-Flow and Loans, I propose how to do something similar but not with Excelsior this time. The idea is to write in a high-level "pseudo-code", get that right, and only then translate into Excel. But the first two require a special program, and the third requires great development discipline. What they really show is that you should not use Excel in any project which needs its internal workings to be documented.
Since the previous entry refers to one piece of spreadsheet-replacement software, I reckon it's OK to mention my own Excelsior, which generates.spreadsheets from input files that describe spreadsheets as collections of tables, related by formulae. If a spreadsheet needs many tables all the same size and shape, you can make these all depend on the same constants in the input file. Changing the constants will resize all the tables. In How To Avoid Hours Of Tedious Spreadsheet Editing, I tell how I used Excelsior to resize the 60 20-by-40 tables in a 10,000-cell spreadsheet that modelled stocks of social housing, so that we could generate new spreadsheets with differently sized tables in just a few minutes. As with the previous blog entry, the point is that the need for other software demonstrates something Excel is bad at: having lots and lots of tables whose sizes need to be changed together. More generally, Excel just is not very good at producing "families" of spreadsheets, identical except for the shape and size of certain regions.
Yet another link from Patrick O'Beirne, this time to Cool Anago Demo in Thomas de Nooij's blog Thomas on Spreadsheets . Thomas is writing about Anago Assemble, which enables users to specify business calculations visually — "without programming" — by graphically "wiring" inputs, calculators, and outputs together. The point of it for "How NOT to Use Excel" is that Thomas justifies using Anago Assemble by mentioning something that's hard to do in Excel: create a spreadsheet that calculates Sales per product from Units Sold per Customer, Product and Month. This needs a three-dimensional table. But Excel doesn't have three-dimensional tables. OK, so you flatten the table. Simple? Thomas explains why it isn't.
Patrick also pointed me at 10 worst Microsoft Excel practices .from the The Universe Divided blog by Michiel van der Blonk. Michiel identifies problems such as using too many worksheets — one for each month in a financial report, for example — which makes it much harder to analyse the data than if it were all in one sheet.
Patrick O'Beirne of Systems Modelling Ltd sent me a list of articles about how not to use Excel. The first that I'll mention is his own The Devil's guide to spreadsheet creation, twenty rules for writing bad spreadsheets. Some will resonate with every programmer:
Don't obtain test data; whatever the spreadsheet result is, is right.and
Documentation is for wimps; specifications are for the timid.And, of course:
Just do it. Jump in and do it. The users will have to accept whatever you produce anyway.
I seem to have started something. Mailing the Microsoft Excel Developers List EXCEL-L this morning about John Meyer's "Off Topic" humour posting which recommended Don McMillan's hilarious video How NOT to use Powerpoint, I said it would be good to see something similar for Excel. Nobody made a video, but huge numbers of readers posted about their pet hates: merged cells; "deleting" cell contents by entering a space; formulae like =SUM(A3+A4); leaving #DIV/0! in cells; ghastly colours...