Best practices for writing spreadsheets.

[Sherwood Botsford]

I commented in another post that Excel is in the BASIC/FORTRAN/assembler spaghetti code stage of development.

Is there an equivalent of a book that does for spreadsheets what Wirth's "Data Structures + Algorithms = Programs" did for procedural languages.

How do you write spreadsheets that another person can understand?

I can come up with partial solutions:

* Used named ranges. Even for single cells. Give them meaningful names. This gets into a named range management issue.
* Insert extra columns. Use them for comments. (Commenting the cells themselves will confuse the user of the spreadsheet, who wants to use comments to draw attention to what the sheet is telling. Hide these columns before presenting to the user.
* format code what a cell does. Even if is as simple as any cell with a formula result is 2 points larger in bold.

But these are all stopgaps. It's like saying "Put lots of comments in your basic code" "Don't use 2 character variables in Fortran.

What is the equivalent of structured programming for spreadsheets?

 

[NARAYANK991]

Hi Sherwood ,

If you type in :

structured spreadsheet design

in Google , I think you will find the results encouraging.

In fact Chandoo had initiated a series on this sometime back ; the first blog post is here :

http://chandoo.org/wp/2012/08/29/best-practice-modeling-5tips/

Narayan

 

[jeffreyweir]

@Sherwood:

How do you write spreadsheets that another person can understand?

And then there's this question:
How do you write spreadsheets when you don't know how Excel even works?

...which is why I wrote this post: http://chandoo.org/wp/2014/03/03/handle-volatile-functions-like-they-are-dynamite/

Is there an equivalent of a book that does for spreadsheets what Wirth's "Data Structures + Algorithms = Programs" did for procedural languages.

If there is, I don't expect spreadsheet programmers to read it. Too dry. Your general user doesn't want to read something very conceptual. They don't even know they are a programmer. They think they have little interest in investing any time or money to become better programmers. (And some of them do have zero interest...the type of people that post here saying "Can you give me a formula that does x" and then use the formula without understanding how it works. Nothing wrong with that, mind. Apart from the fact that it soaks up lots of our time in answering their questions if they don't learn any concepts from the answers we give.)


With my book, I aim to sneakily introduce some programming concepts...just enough to get them interested in learning more.

There are already quite a few books out there on how to construct spreadsheets to best practices that others can audit. I don't want to write a book like that. I want to write a book that lots of people will actually buy. I want to write a book that is the gateway drug to them then buying the additional books they actually need.

 

[jeffreyweir]

This is the best praise I ever got from a blog post:

Laughed my socks off, but more importantly, learnt a massive amount. Given me a whole new perspective on the development of dynamic reports and charts.

This is really the way applications should be taught.

Forget something exhaustive. We need a book on Excel that entertains.

Here's another comment on that post:

Great post. I laughed, I learned. What else could I ask for on a Friday morning.

Check that post out. The book will be in a similar approach. Approachable.

Not everyone will appreciate it.

 

[Sherwood Botsford]

Good call, Narayan.

Lots there. However little that is of real use.

Here is commentary on the first 5 hits I got off googe.

Twenty Principles of Good Spreadsheet Practice
http://www.ion.icaew.com/ClientFile...16120898a/Spreadsheet principles - public.pdf

Vague generalities. Don't bother.

***************************************************
Spreadsheet Issues: Pitfalls, Best Practices, and Practical T
https://www.soa.org/library/journals/actuarial-practice-forum/2010/february/apf-2010-02-campbell.pdf

Sets of practices, but not really structured spreadsheets.

***************************************************

A Structured Methodology For A Structured Methodology For Spreadsheet modeling... - arXiv.org
http://arxiv.org/pdf/0805.4218

Good article showing a modular approach using Jackson charts

The Art of the Spreadsheet
http://john.raffensperger.org/ArtOfTheSpreadsheet/Chapter01_WhySS_StyleIsImportant.html

Concentration is on style, but at least in chapter 1, I found the recommendations too vague. e.g.:

How do we implement these general rules? By following these steps:​

1. 1. Make your spreadsheets read from left to right and top to bottom.
   2. Be concise with sheets.
   3. Be concise with blocks.
   4. Be concise with blank space.
   5. Be concise with cells.
   6. Format for description, not decoration.
   7. Show all the information.

Building structured spreadsheets - IDEALS Home
https://www.ideals.illinois.edu/bitstream/handle/2142/29133/buildingstructur1566ston.pdf?sequence=1

Another one that is full of vague principles with no clear way to implement them.

Analytic Solutions - Eliminating Risk in Spreadsheets
http://www.analyticsolutions.com/index_files/Page1227.htm
Handful of tips, but no overall methodology.