 |
When you have to create your own
spreadsheets out in the real world, it is not likely that you will be given
specific directions like you are in these lessons. In fact, you may be given
just a vague statement of the main goal.
The points below will help you design a workable
spreadsheet. Thinking through the list at the beginning can save you a lot
of time and trouble before you are through. Basically you work backwards
from the end result that you want to figure out what you need to have to get
there.
Keep in mind the two kinds of spreadsheets - the sheets to which you add data regularly, like inventories and payroll calculations, and the sheets which summarize
the state of things at a particular time, like quarterly or annual reports which
you will not want change
|
|
Where you are:
JegsWorks >
Lessons >
Numbers
Before
you start...
Project 1: Excel Intro
Project 2: Excel Basics
Project 3: Format & Arrange
Project 4: Groups & Formulas
Project 5: Design
Analysis
 Analyze
Documentation
Comments
Print
Comments
Planning
What If...
Logical Tests
Sharing Data
Summary
Quiz
Exercises
Search
Glossary
Appendix |
|
Planning Checklist for Spreadsheets
1. Set Goals
What is this spreadsheet for? What questions will it answer?
2. Identify Output and Input
A. Output - Final values that the
spreadsheet will produce, like Total Sales, Average Price, Number of Members,
Number in Stock.
B. Input = Values Data you need in
order to get the results you want, like Price for Each, Tax Rate, Name, Address,
Item Number, Quantity, Amount
C. Input = Formulas Functions and
formulas that you will use to calculate the Results. Write these down in
terms of the Values you listed. For example, Number in Stock = Number
Bought - Number Sold, Selling Price = Our Cost + Overhead + Sales Tax.
3. Design Layout
You must handle four activities well to achieve a good layout for your spreadsheet.
A. Entering Data Make it easy to enter
data. It is easier to enter data accurately when the spreadsheet uses the same
order that the raw data comes in.
B. Using the Sheet What paper or
screen size will you use? Is the sheet easy to read? Is the most important
information easy to find? Can the user see the row and column labels for long
records?
C. Changing the Sheet Later Will new records
be added? Might you need to add rows or columns? Formulas
must be carefully designed to be sure they will work right after adding new
data.
D. Printing Will the spreadsheet ever
be printed? What size paper? Print all or just part? Must the printing fit on a
certain number of pages? Should you repeat column and row labels on every page?
Are gridlines needed? What should be in the Header and Footer? Black and White,
Gray Scale, or Color? How well do your color choices work in each? Is the sheet
still readable? Is there color coding that is no longer helpful once printed?
4. Test Calculations
A. Easy numbers Try out your spreadsheet with data
that is easy to calculate with, so you can check that the results are correct.
B. Known Numbers Try out your spreadsheet with realistic data for which you know the results.
C. Special Numbers Try special case data, like zero
values, blank cells, very small numbers, and very large numbers. Are the
columns wide enough to show the largest and smallest numbers you can expect to
see? Does your spreadsheet handle these special cases well or does it go
crazy? If error messages appear, do they explain what to do?
5. Document what you did
Either directly on the spreadsheet or in hidden comments, provide information about your work. Your documentation covers three kinds of information:
What is it? What is this spreadsheet
for? Who designed it? When? When was the data last updated? Where did the data
come from?
How to use it. Where to enter new
data? Where to find results? How to enter new data? For example, do you enter a
phone number like 1-222-333-4444 or 1 (222) 333-4444 or 12223334444 without any
punctuation? Do you enter amounts of money as whole numbers only or should you
include the decimal part? How to change it. Explain what you did and why, especially your formulas and what parts of the spreadsheet depend on other parts. It is amazing how much you can forget about your own work after a few weeks. So write up details that you think you won't
forget. It will be worthwhile in the long run.
|
