Spreadsheet Design:
Test Your Sheet

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

Even when your sheet looks good with one set of numbers, it may fail with a different set. You cannot test all possibilities, but you can check out the most likely problem numbers.

Zeros and blank cells can cause trouble in poorly designed formulas. Values that cause dividing by zero must be avoided! Very large and very small numbers may not fit in the space allowed. Adding new rows can break a formula.

In the steps below you will check out the fit to the screen, the health of your formulas, and the fit of your numbers to your columns.

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
    What If... To subtopics
    Logical TestsTo subtopics
    Sharing DataTo subtopics
    ExercisesTo subtopics


Icon Step-by-Step

Step-by-Step: Test Your Sheet

 Icon Step-by-Step

What you will learn:

to test fit to screen
to test with easy numbers
to test with special numbers
to document the sheet

Start with: Class disk trips34.xls (saved in previous lesson)

Test Fit to Screen: 640 x 480

If your monitor is using a resolution than larger 640 x 480, you cannot tell by looking at your screen if your layout will fit at 640 x 480. You need to either change resolutions on your monitor, or resize the browser window to the resolution you want to check. Use one of the following to see how your sheet fits:

  • Switch resolution with your video card software: Some video cards allow you to switch the resolution of the monitor without having to reboot the computer. If yours does, you are in good shape! The choice is often in a popup menu from an icon in the Tray at the right of the Windows taskbar. Otherwise, check out Display Properties (right click on the Desktop and choose Properties). The Settings tab will show what resolutions you are set up to use. However, you may have to reboot to change the resolution. It's not worth doing that. Use another method instead.
  • Switch resolution with other software: There are other programs that let you switch the monitor resolution, like the Power Toy QuickRes.
  • Set browser size with software: Programs like Browser Sizer open a browser window for you that is a particular size, like 640 x 480. Very handy!
  • Set browser size manually: Make an image that is the right size and resize your window the same size.

    Open Paint. Resize the blank default canvas to 640 pixels wide and 480 pixels tall using Image | Attributes. (Pixels are called pels in the dialog.) Fill the blank white canvas with a bright color. Switch to Excel with the sheet you want to check as the active sheet. Maximize the sheet in Excel's window. Do not have Excel itself maximized. Position the upper left corner of Excel's window on top of the colored image in Paint. Drag the edges of Excel's window until the whole window is the size the Paint image. Ta Da! What you see in Excel's window is what you would see at 640 x 480 resolution with your current toolbars showing. You can save your Paint image to use later if you need to do this kind of check often.

If your sheet is too wide for the screen, what can you do about it?
Some possibilities:

  • Reduce the width of one or more columns
  • Reduce the font size and then the width of one or more columns
  • Rearrange your sheet

Test Calculations: Easy Numbers

Your formulas look like they are working while using this "realistic" data. But it's hard to be sure. It would be easy to have dropped a zero or to leave out a part of a formula or accidentally stuck in an extra cell reference while clicking around. You need to check your formulas with numbers that are easy to work with. In fact, it would make a lot of sense to start out with really easy numbers and then test with realistic data later.

  1. Before you continue, make a note of Gardner's numbers. You will need to put them back in after testing is done.
  2. Replace the Assumed Values: Use 10, 10%, 10%. Did the concatenated labels update?
  3. Replace Data Values: Replace the # Tickets Sold with 10, 10, 10 and the Sales of Other trips with 10000.
  4. Check Calculations: Widen column G to see the calculations. Since you used numbers that are easy to calculate with in your head, you can easily see that the Points and Total Points are working well. Only the Sales in G4 is not obviously right or wrong. Do a little hand calculation to see if that is correct.

    Tahiti = 10 tickets x $1500 = 15000
    New Zealand = 10 tickets x $3000 = 30000
    World = 10 tickets x $6000 = 60000

    Other = 10000

    Total = 15000 + 30000 + 60000 + 10000 = 115000. Correct!

    What if Bonus sheet - completed

Test Calculations: Special Numbers

Before you can say that your formulas are definitely working right, you must check out some special situations. What happens if some of the numbers are not there? Will the spacing be big enough for the largest and smallest numbers likely to be used? Test it out!

  1. Zeros: One at a time, replace each of the data numbers in the Light Turquoise or Tan cells with a zero. (Do not forget to press ENTER to actually enter the value.) Does the table still give good answers? Undo between each change.
  2. Large/Small Numbers: Change the number of tickets sold for Tahiti to the largest number you think is likely to be sold. Are the cells for calculated values wide enough? If not, widen the column. Try some other large values in different parts of the sheet.

    At some point the Bonus and Total Commission will not fit in the column. The decision to make is which is more important- keeping the tables on one screen at 640 x 480 resolution, or having the cells wide enough to show really large sales? It's up to you.

    Are there formatting changes you could make to keep the tables on one screen and on one printed sheet and have still wider columns? So many choices!

    You should also test very small numbers, especially when negative numbers make sense on the sheet. This sheet would not use negative numbers, but the commission rate might be very small. What might be the smallest percentage likely to be used for the commission and bonus rates? How about small sale numbers? Try some to see if they are handled well by your formulas and cell sizes.

  3. Return the data to the values for Gardner. [Multiple Undo or close without saving and re-open]


Your spreadsheet is designed, formatted, and tested. All that remains is to document what you did. Some documentation was done when you created titles and labels. Now you must create the Comments that will explain how the sheet works, who did it, etc.

Comment - for documentation

  1. Main Comment: Select cell A1 and insert a Comment. Format the shape and color of the Comment's text box to suit yourself.

    In this comment include -

    • your name
    • date completed
    • class for which it was done
    • Purpose
    • Planning Spreadsheets steps 1 and 2 from the previous lesson Create a What If Sheet. (Of course, your comments will not be exactly like the ones in the illustration.)
      • Goals
      • Output
      • Input
      • Formulas
      • Design Considerations

  2. Other Comments: Select at least one cell containing a formula and create a comment that explains the formula in words rather than cell references.
  3. Page Setup: Open File | Page Setup | Sheet and set Comments to At the end so that all comments will be printed on a separate page after the sheet prints.
  4. Header: Create a header with your name and the date on the left, the file name and sheet name in the center, and Excel Project 5 on the right.
  5. Spell Check. (This will check your comment text, too!)
  6. Check the Print Preview.
  7. Class diskSave as  trips35.xls  to the excel project5 folder on your Class disk.  
  8. Print Print the Bonus sheet only. You should get 2 pages, the bonus calculator and the comments.

Print-out: Bonus

Print-out: Comments for Bonus sheet