Did you want Working with Numbers: 2007,2010,2013,2016  or español

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

Project 2: Excel Basics

Project 4: Groups & Formulas

Search
Glossary

Appendix

#### 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?