A good design for your spreadsheet makes it easy for you:
Good design does not have to be pretty or colorful, though that often helps. A well-designed sheet will be comfortable to work with and makes it easy to answer the questions you want to ask.
How can you tell if a worksheet has a good design? The questions below are a good guide.
To analyze the effectiveness of a spreadsheet's design use the following questions about the spreadsheet. With good design you can answer these easily. A poor or bad design makes it hard or impossible.
What's wrong with the spreadsheet below? Try to answer the analysis questions: Purpose, Data, Calculations, Changes.
You can't tell anything about what is going on in this sheet! You cannot answer any of the analysis questions. The only good thing about the sheet is that it uses the default font, so you can read the values.
No titles to tell you what this sheet is about.
No column labels to explain all the numbers.
Not clear whether any of the numbers are calculated.
The numbers in the last two columns are hard to read and compare to each other because they are not lined up on the decimal point.
Simple Solution: Adding some simple titles and labels makes the spreadsheet much more understandable, as the version below shows. Now can you answer any of the analysis questions?
It is still not really easy to read the numbers in columns F and G, and you can't tell if theentries are calculated or typed in.
Even Better Solution: Some formatting and expanded labels and titles will help even more, as the next illustration shows. Can you answer the analysis questions?
Now you can see that theis a calculated value using the and . Moving the over next to the Pay Rate puts the values used in the formula next to each other, which makes it easier to see where the comes from - and why some paychecks are bigger than others.
This spreadsheet is an example of one that you would not normally have to change later. It is a report of the values at a given date. However, a similar spreadsheet that logs the hours worked each day for each worker would have new data added every work day.
Here is a design problem common when rows and columns are both being calculated. Can you answer the analysis questions above for this sheet?
What formulas? Row 8 and Column F look like they could be calculations.
Is cell F8 related to the row values, the column
values, or something else?
Use formatting for F8 that matches the source data.
Add labels and other formatting to group related cells. In the revised sheet you can see that F8 is the SUM of Row 8.
From the examples above and some common sense we can come up with some principals and guidelines that will help you design worksheets that work well.