Spreadsheet Design:
Planning a Spreadsheet

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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 IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: Design
    Analysis To subtopics
    FootprintPrint Comments
    What If...To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    ExercisesTo subtopics


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.