Recently, I was working on a complicated spreadsheet for an important client. I showed my spreadsheet to Grace Gridwright.
Here's what she said.
"This thing stinks! Boss, you need to go back to style school."
Why? What's wrong with it?
"Look, you've got formulas that point all the wrong ways.
What do you mean, 'point all the wrong ways'?
"For example, cell B1 refers to cells below!"
So? What's wrong with that? And what are those blue arcs?
"Well, it's just goofy, that's all. The arcs are the arcs of precedence, which I put in with Excel's Auditing Toolbar. You can read more about the Auditing Toolbar on our Auditing page."
Hmmm, could you give me a little more reasoning than 'it's just goofy'?
"OK. If you're reading the spreadsheet for the first time, you will probably start in the top left, just as if you were reading a web page. Then, you would read the next couple of blocks, as though they were paragraphs.
"Since we read from left to right and top to bottom (in the Western languages), we expect each block of information that starts at the top to be self-contained. You should be able to delete everything below without disturbing the information above.
"If a paragraph in a web page says, 'See above for more information,' it would be, like, strange. We already finished reading the part above! If a table refers to something below or to the right, it's a surprise, I guarantee it. It contradicts our natural expectation."
Wow, I said. That seems like a really strict way to write a spreadsheet. I thought the whole idea of a spreadsheet was flexibility and freedom.
"Yeah, you can write badly with a word processor, too. Flexibility is good, but a bit of organization will make it a lot easier to read."
"Besides, spreadsheets can recalculate faster if they are written from left to right and top to bottom. Plus you are much less likely to create circular formulas, because something would have to point backwards to be circular."
Interesting, I said. I hadn't thought of that.
"One more reason, boss. If you write your spreadsheet this way, it will be naturally organized to follow the business logic. You don't need artificial means to structure a spreadsheet - like sheets with 'input-calculations-output.' Just make the formulas flow from left to right and top to bottom, and you will be pleasantly surprised how well it works out."
All right, I said. So how would you re-write this spreadsheet?
"I would do it more like this."
Cool! I said. Everything flows downwards. Now I see that the 7.8 is unused. Perhaps I should have seen that before. But organized this way, it kind of stands out.
Say, that 7.8 is a constant. Shouldn't it go at the top?
"Well, many people like to put all the inputs at the top. However, I have noticed that the 'input-calculation-output' approach tends to create all kinds of readability problems. The main idea is to put the 7.8 right where it is used, right next to the cells that refer to it. The precedence arcs should be as short as possible."
Hmm. Could you expand on that?
"Sure! Click here to see Tip 2: Have short arcs of precedence."