Cool Spreadsheets

Spreadsheet Design Tips

Does your spreadsheet look like this?

The screenshot above is a fairly typical look and layout for a spreadsheet—bland, no character, not visually appealing. You’re not going to “WOW” anyone soon with your spreadsheet capabilities. On the other hand, taking a few minutes to dress up your spreadsheet with the 8 design tips that follow will make a tremendous difference in how the world perceives your data. Not only will the spreadsheet look cool, it will have more validity, it will likely get more attention and focus, and will most certainly impress the boss. Later, we provide a screenshot with all of these tips applied...

Tip #1: Add Margins

Always leave row 1 and column A blank. This creates a margin that sets your data apart from the row and column borders. Also, reduce the column width for Column A down to about 2.5. This approximately matches the width of column A to the height of row 1 thus creating an even top and left margin.

Tip #2: Turn OFF Gridlines

Gridlines make the spreadsheet look too busy. In Excel click Tools, Options, View, and uncheck the Gridlines box. In OpenOffice.org Calc click Tools, Options, OpenOffice.org Calc, and View, and then uncheck the box for “Grid lines.”

Tip #3: Add Cell Borders

Add cell borders to highlight your data--this is similar to gridlines but is restricted to where you apply it, rather than the whole spreadsheet. Avoid using black borders except in strategic locations to separate key sections of data. The spreadsheet makeover below uses a light grey border that does not overpower the data and uses only two black borders for intentional effect.

Tip #4: Add a Header

If the spreadsheet pictured above belonged to you, the data would mean something to you today. But, are you going to remember what it means one year from now? What happens if you share the data with someone else—will they know what the data means? Add a header--a title, a revision date, your name, and whatever else is appropriate--above your data. The title should be in a larger font size. The built-in header feature* is fine for printing but is useless when viewing a spreadsheet on screen.

*(In Excel this is located in the View menu by clicking “Header and Footer”) (In OOo Calc this is located in the Edit menu by clicking “Headers and Footers”)

Tip #5: Add a Splash of Color

Add one or two colors, at most, to your spreadsheet in strategic areas. Here, we are talking about cell shading, not text color. Stick with black text except against a dark background where white/bold may be more appropriate. Generally, use lighter colors for cell shading.

Tip #6: Add Footnotes

Explain your data! What are the assumptions you made, the sources of your data, or points that need clarification? Help the user understand what is going on. Notes also help you remember what you did when you open the spreadsheet a year from now.

Tip #7: Use a Clean Font

Data in a spreadsheet is easier to view when using a clean, simple font like Arial. Compare that with Times New Roman and you will see a significant difference in look and readability.

Tip #8: Use Conditional Formatting to Tell a Story

Did you meet your sales goal? In the spreadsheet above you have to scan each month’s sales against the goal. With conditional formatting (red/bold in the spreadsheet makeover below) a quick glance makes it immediately apparent which months did not meet goal.

Here is the same spreadsheet as above with each of the 8 design elements applied: