Nov 27 2008
02: Spreadsheet Design
There are perhaps thousands of books on using Excel, but only a tiny handful discuss spreadsheet design. We’re going to start here because it’s crucial for readability and clarity, especially if anybody else needs to see your spreadsheets and interpret them.
Nobody should ever look at your spreadsheet and say, “Huh?”
Organize your data. Put revenues with revenues, costs with costs, constants with constants, variables with variables, and so forth. Arrange your data in organized clusters, use clear, unambiguous labels, use blank rows and columns to separate the clusters (and label them), and finally use formatting (judiciously, please) to highlight your data and make your spreadsheet more readable (note that right-aligned labels are hard to read).
Yes, yes, yes, I am as impatient with nonsense as anyone. I used to refer to the day in the first week of the semester when we covered creating charts as finger painting day. But formatting doesn’t exist to make your spreadsheet pretty; it exists to make it readable.
List all named ranges in your spreadsheet. I prefer to arrange mine alphabetically, but some arrange theirs by location. Whichever you prefer, as long as they are clearly listed so the reader can immediately see what cell or range each of your names references.
Note that because I despise Photoshop and graphics in general that I may violate my own design principles from time to time. Life is like that. It’s the only way I’ll slog through this.
Here, for example, is a well laid-out spreadsheet. Note the spacing, the use of formatting, and the comment and text box to clarify the content.
A comment does not pop up until you place the cursor on the cell containing the comment (such a cell has a red triangle in the upper right corner). Use comments to annotate otherwise clear data. A text box appears on your spreadsheet (they cover up cells, unlike comments, so place them cautiously).
Separate related data on different worksheets. If you have charted your data, place the chart on its own worksheet. If you have the same data for different months, place each month on its own worksheet. Label the worksheet tabs (right-click on the worksheet tab) clearly, so nobody has to guess what each may hold (note that this includes you, when you find that file you created a few months ago, and open it).
Italics are hard to read, except in short bursts, so use italics judiciously. There is no reason to use more than one font. Don’t lose your mind playing with formatting. Forget pretty. It’s about transparency.
That’s all I have to say about that. They’re general principles, and you can figure out what works and what doesn’t.
