1. The "Write Like English" Rule.
Make your spreadsheet read from left to right and top to bottom.
1. The "Write Like English" Rule.
Make your spreadsheet read from left to right and top to bottom.Since we read from left to right and top to bottom, your spreadsheet will be easier to understand if formulas generally point to the left and up. A formula that depends on cells below or to the right will tend to confuse your reader. More details.
As with text and graphics, readers want to see related information close together, in context. If related cells are far apart, the context is broken up. Put related cells together.
Don't have a "data block." Instead, put constants close to the formulas that use the data. More details.
Attempts to convey information with color (other than contrast) tend to fail. Your reader will not memorize the meanings of six different colors. If you must use more than three colors, consider using shades of gray. Shades of gray more naturally convey magnitude.
One of your Three Crayons should be used for making Glowing Formulas.
A pleasant approach is to format formulas with a very light gray. Unfortunately, Excel does not put the nicest gray on its default palette.
First select Tools, Options, Colors.
Select one of the ugly-colored chart fills, then click Modify.
In the Standard colors, choose the very lightest gray that is not white, then click OK, and OK again.
Now use Edit, Go To, Special, and tick Formulas, then click OK.
At this point, all the formulas in the sheet (not in the whole workbook, just this sheet) are selected.
Select Format, Cells, Border, Color Gray-40%, then Outline and Inside.
Select Patterns, then your new very light gray. Click OK.
Type the word "Formula" into a cell at the top, and format it like the formulas.
Now all your formulas are in light gray and will be easy to find. Your reader will see immediately which cells are formulas and which are constants.
Right justify numbers, so the decimal places line up. Right justify the labels above the numbers, so they are not confused with an adjacent column.
Except for labels at the column tops, try left-justifying all text. Readers are accustomed to seeing left-justified text, as in a book, so it will probably look better. Justify column headers so they match the numbers below.
Everything takes time, even looking for blank. By showing where the cells are, you help your reader see the spreadsheet's structure. While you're at it, try to have all columns (except for the A column) be about the same width. It will look much better.
In a spreadsheet, a cell is an object and a container. Unless you keep your tables very well aligned (which is a very good idea, it's just hard to do), confusion easily arises without the grid. Your reader will have trouble determining where the cells are located, and will have to move the cursor just to find them.
There are many insidious tricks to hide information. Here are a few ways to make sure your information is displayed:
Don't format the color of the text as white on a white background. Very creepy!
Don't format a cell as hidden.
Don't use a formula to hide an if-then-else clause:
= if (something, show thing, "")
This makes the cell look empty if "something" is false. Expose the information. Or at least use a formula such as
= if (something, show thing, ".")
This way something shows up, so the reader knows the cell is active rather than blank.
Don't start a label with spaces, because it makes the label appear to be in the adjacent right column.
' Super Dooper Spreadsheet
Show which cells have information and which ones don't. Make an active cell look like it is active.
OK, like, wow, this is really a big one. Hey, pal, you ever hear of my man Strunk and my man White? See, they wrote this cool book, tome, thingy, whatever you want to call it, and it's the greatest for if you are doing any writing, because (Did you know how cool spreadsheets can be!?) if you're doing writing, they have a whole lot of rules and ideas and stuff that can really be a big help! Anyway, this book is called The Elements Of Style and one of their really big points is that you should try to say (or write) whatever it is you feel is on your mind, in a way that uses just the fewest and the least number of words and sentences that you really need!
Common wisdom is, "Break complicated formulas into several cells." This is generally bad advice. Try to use as few cells as necessary. Better yet, minimize the total number of characters in all formulas. Encourage your reader to focus on only what is important.
How can you be concise? Several ways, but the most important things are numbers 9 and 10, next!
Which cells are unnecessary? There are several types.
Delete cells that are just "for your information," that do not drive towards the main point of the spreadsheet. Any cell with no dependent is a candidate for deletion.
Delete cells that are used just for validation, to check whether your spreadsheet is correct. These are useful when first writing your spreadsheet, but delete them after you have checked the formulas, so your reader does not have to see them.
Delete spurious cells. A spurious cell simply points to another, as in "D6=B24". This really confuses your reader. It most commonly occurs when you use multiple sheets, because you want to show an important bit of information that is the same as on another sheet. Don't. Put each bit of information in exactly one place, and never repeat yourself repeat yourself repeat yourself repeat yourself.
Don't interpret output. You may be tempted to show the cool things you can do in a spreadsheet, as in the horrible formula:
IF(B6<0,round(-B6/1000,0)&" thousand loss",round(B6/1000,0)&" thousand gain")
Eeewww! Just label B6 properly in the first place. Don't interpret output.
I saved the most controversial for last. Using multiple work sheets causes the most amazing usability problems. What sort of problems?
The main reason to put your spreadsheet on a single sheet is so that your reader has the chance to see everything at a glance. Perhaps your reader will set View at 25% to get a bird's-eye view of the structure. At least he has a chance. If you put the spreadsheet on multiple sheets, he is forced to clicky-click to see it all, and then only in pieces. There are many other reasons! More details.