The Art of the Spreadsheet

Down and to the right.

John F. Raffensperger

The Art of the Spreadsheet

Copyright 2008 John F. Raffensperger

I've been thinking about this material for many years, but have only recently decided to post it. I don't have time to post all of it in one go, so please bear with me as I add pages over the coming weeks. The pages are designed to be print in book-like form, and eventually, I'll post a single printable document. - jfr, 24 March 2008.

Preface

Tufte, Edward, The Visual Display of Quantitative Information, 1983, Graphics Press, Cheshire, Conn.

An ideal manual of spreadsheet style would read like Edward Tufte's marvelous book, The Visual Display of Quantitative Information. In a hand-designed and printed volume, he provides dozens of elegantly printed examples of good and bad graphics, taken from the popular and scientific press. Unfortunately, it has been rather difficult to imitate his style, for several reasons.

First, unlike graphics, spreadsheets are rarely published, so few examples are publicly available. So it is difficult to acquire a body of material to critique.

Second, a manual of spreadsheet style must show images of the computer screen. But computer screen bitmaps do not yet approach the quality of Tufte's beautiful very high resolution graphics, simply because computer screens do not yet provide the resolution of print. Perhaps in a few years.

Third, Tufte spared no effort or expense to have the finest graphics. Unfortunately, most of us have humbler tools - laser printers and spreadsheets or word processors. The aim here is to produce good results with tools that most of us have readily available. Graphics must be reasonably producible. That is, they must take a reasonable amount of time to construct, require only typical computer software, and be printed with widely available machines, e.g. a 600 dpi laser printer. Graphics must also be reasonably reproducible. They should copy well with a good office photocopier. So the nature of this work suggests that we are at a disadvantage to Tufte from the start.

Tufte, Edward, Envisioning Information, 1990, Graphics Press, Cheshire, Conn., p. 10.

Still, I freely borrow from Tufte wherever possible, and I hope this little volume proves half as valuable as his. Spreadsheets are written for different purposes, but the goal here is to write timeless advice that can be applied to most spreadsheets, just as “Write concisely” might apply to writing any text. As Tufte put it, “The principles of information design are universal - like mathematics - and are not tied to unique features of a particular language or culture. ”

Generally, I will avoid the word “user.” The analogy to written expression provides clearer terminology - writer and reader.

This book will not cover graphs, macros, or Visual Basic. There is already enough here for one book. Perhaps those can be covered in future volumes.

A special thanks to Linus Schrage and the gentle folk at LINDO Systems for their kind support.

Table of Contents

1. Why is spreadsheet style important?

"Writing a spreadsheet is like writing a computer program"

Managers do not want the current style

What style should a spreadsheet have?

Spreadsheets should be written for readability.

The new spreadsheet style.

2. Make your spreadsheet read from left to right and top to bottom.

The arcs of precedence.

Have short arcs of precedence.

3. Omit unneeded bytes.

4. Omit unneeded sheets.

5. Organize blocks with care.

Align data types consistently and align related types together.

Align the primary data type downward in rows.

6. Attend to blank space.

Keep the grid on.

Make active cells look full. Make empty cells look blank.

7. Omit unneeded cells.

Eliminate spurious cells.

Erase dangling cells.

Eliminate relics.

Nest and erase formulas where appropriate.

Simplify formulas.

Do not put constants in formulas. Make formulas reference constants.

8. Format with caution.

Format for description, not decoration.

Use numerical formats liberally.

Use formats of perceived necessity sparingly.

Format constants differently from formulas.

9. Show all the information.

Thirty-seven ways to hide information in Excel.

Put labels on the left.

Spell out labels correctly.

You don't need inserted comments.

Use range names sparingly, if at all.

Never hide cells.

Take the time to get it right.

10. Spreadsheet errors.

Wrong inputs.

Accidental logic.

Wrong thinking.

Accidentally overwriting a formula.

Software surprises.

Maybe you shouldn't use a spreadsheet.

11. How to audit a spreadsheet.

Step-by-step spreadsheet re-writing.

12. Suggestions for operations researchers.

13. Teaching the art of the spreadsheet.

Appendix. Checklist for a spreadsheet.