March 13, 2013

Stop using spreadsheets

If you use spreadsheet software, but you don’t use any mathematical functions, please stop right now. You’re inflicting pain on yourself and your readers.

Ugly presentation

By using a spreadsheet for purposes other than doing calculations, you’re choosing to skip the formatting that you would normally do for a document you expect to print out. Because you’re not thinking about formatting, you’re sacrificing legibility. For example, Excel doesn’t smoothly scroll horizontally across the page. The jarring cell-by-cell jumps are really unpleasant for your readers. Why are you presenting the data in a way that forces your readers to scroll?

A document has one author, but several readers. As the document author, the onus is on you to make your document as legible as possible. Wikipedia describes the message of Steve Krug’s book Don’t Make Me Think like this: …a program or web site should let users accomplish their intended tasks as easily and directly as possible”. Exactly the same rule applies to documents as it does to programs.

The context is separate to the spreadsheet

I can’t remember the number of times I’ve received a spreadsheet whose explanatory text is in the accompanying email. Without the covering email, the spreadsheet loses important contextual information. What does all this stuff mean? Who is it important to? Where do I start? Is there any content on any of these other unlabeled worksheets? No? Why haven’t they been deleted?

No version control

By disseminating the spreadsheet by email, you’ve just created multiple competing copies of your document.

To your reader, the spreadsheet they have in front of them is the canonical version. Trouble is, they’re probably editing it right now to provide you with updated or corrected information. Let’s say they email their edits back to you (and the distribution list). Now you have the problem of manually merging their changes to produce the most up-to-date version. What did they change? Better check every row! Let’s assume you do the merge (without missing anything), and email the updated spreadsheet around to everyone again. Now there are 2 x (recipient list + you) copies out in the ether. Remember what I said about the canonical version of the spreadsheet being the one in front of your readers right now? Human fallibility being what it is, the chance that everyone is looking at the most up-to-date version is close to zero.

Microsoft Office does have reviewing and rudimentary version control built in, but these features don’t fix the problem of multiple stale copies of your document continuing to exist forever in email folders and saved in random places on other people’s desktop hard drives - or worse - network shares.

Tools like Sharepoint (primarily a web-based document repository which poorly emulates elements of wikis and version control systems) attempt to address this; instead of emailing the document, you save it to Sharepoint and email the link instead. However if your recipients open the link in any browser other than Internet Explorer, they lose the ability to use Sharepoint’s locking and versioning features. And you can’t control what browsers your recipients use.

Using a dedicated source-control system isn’t the answer either, because spreadsheets are binary blobs. Version control systems are designed to work with text, and only store the differences between successive versions, so that comparisons can be made between versions.

The solution

Use a wiki. This solves all of the problems I’ve described, quickly and easily. There are many wikis out there; some of them can be up and running in minutes. Many offer rich-text editors. Others support only Markdown, a simple text markup language.

Just enough presentation

Wiki pages can be as pretty as newspaper layouts; take a look at any Wikipedia page. You’ll see a table of contents, probably a boxout containing summary information, and the information on the page presented under headings. Tabular data is easily readable. Editing the tabular data in Markdown is straightforward too, though a little more complex than using Excel. Remember, as the document author, the onus is on you to make your content decipherable, not on your readers to decipher it. Wikis encourage you to format your data, but only to a certain extent. Enough formatting is enough.

In-line context

You’re back in the land of free-text; you no longer have to fight with cell formatting (merge cells, wrap text, text alignment…). You can simply provide an introductory paragraph for your readers, before proceeding to the meat of the data. You were going to write that in the covering email anyway, right?

Built-in version control

There’s only one version of a wiki page - the one at the link you provide. Your readers can edit the page in their browsers and hit Save” - and everyone gets to see their updates immediately, or at least on the next page refresh. If two readers are simultaneously editing the current version, the second editor to save their changes will get a warning that there have been changes to the page since they started editing. The onus is now on your readers to manage merging, not you. If someone over-writes changes accidentally, it’s a simple matter to roll back to an earlier version.

If you really need to, you can enable role-based access control, so that every user has to be authenticated before they can make edits. (…but why would you want this? Presumably you’re trying to foster collaboration and information exchange. Why prevent stakeholders from contributing?)

Summary

It’s easy to reach for a spreadsheet in order to quickly knock out some tabular data - but please think again before doing it next time. Your quickly-assembled spreadsheet is likely to live a lot longer and have a broader readership than you anticipate. Do yourself and all of your readers a favour, install a wiki and use that instead. They’ll thank you for it.


Previous post
What we’re actually doing when interacting with an ‘intuitive’ interface I’ve become pretty cheesed off with the term ‘intuitive’, as in ‘this shiny touch interface is so intuitive’. I get to observe my 87-year old
Next post
Markov chains and formal proofs I’ve posted this link before, when this blog was on the late lamented Posterous, but when I went looking for it again, it had disappeared - so here