Who's reading the spreadsheet?

There are two classes of 'thing' that use spreadsheets:

1. People

2. Computers

Once there is data in a spreadsheet, there are some jobs that only the person can do (e.g. type in new data), and some that only the computer can do (e.g. apply a formula automatically). The things that both can do are the ones to pay attention to when designing data structures. That's where the person's care for the computer's limitations have the most impact.

Here are some examples of things both can do. The computer can autosum a string of numbers. The person, if they really want to, can add up the numbers on a piece of paper (or in their head if they're amazing). Similarly, the computer can sort by a designated column. The person, if they really want to, can do that too - it involves a lot of cutting and pasting, but it can be done.

Here's what the computer can't do:

Arrange things so they're easier for the person. 

Here's what the person can do:

Arrange things so they're easier for the computer. 

At the most fundamental level, the single most helpful thing you can do for the computer is:

Arrange your data so the computer can sort by any column.

And by implication:

Keep your data in the smallest nodes possible.

The computer isn't as smart as the person. It's not smart at all, it has no intelligence. It can only deal with thing it recognises, and that's not actually very much. Similarly, it cannot read for meaning. As far as it's concerned, it only deals with random strings of symbols. So, to make life easier for the computer, here are some tips:

1. Colours are meaningless to the computer, and you can't sort by them. Avoid colour whenever possible, or supplement it (even an extra column with the words 'red' and 'green' in it will improve things).

2. Seperate, seperate, seperate. Never have a column with 'John Smith' in it. 'John' and 'Smith are semantic chunks - one is the chunk 'first name' and the other is the chunk 'Last name'. So seperate them.

3. Use unique ids wherever you can. If all else fails and the data gets messy, you can always sort by this column to get back to the original shape.

4. Never, ever, ever write 'same as above'. Once you sort, that little piece of information becomes a) false and b) misleading. If it's the same, then copy and paste from that cell.

5. Don't merge cells, even if it looks nicer. It's a little bit nicer for people, infinitely worse for computers. Merged cells prevent sort from working.

6. The less formatting, the better. The simpler your spreadsheet is, the better the computer will be able to deal with it.