Spreadsheet Design:
Analyze a Sheet

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

Does the worksheet that you have been developing in these projects have good design?

Answer the analysis questions in the Step-by-Step exercise below.

  1. Purpose:  What is it for? What questions does it answer?

  2. Data: What data is used and where did it come from?

  3. Calculations: How did they do that? (What parts are calculated and what formulas are used to do the calculations?)

  4. Changes: Does this sheet allow you to add or change data later? Is that important for this particular sheet? Will it be easy to do?

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


Icon Step-by-Step

Step-by-Step: Analyze a Sheet

 Icon Step-by-Step

What you will learn:

to analyze a sheet
to record analysis on the sheet
to use key combo ALT + ENTER to create line break inside a cell

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

Analyze Sheet

  1. Open  trips30.xls , if necessary.
  2. Inspect the upper table on the sheet Specials. How good is the design? Answer the four analysis questions (for the upper table only). You will record these answers shortly.

    Is it easy to answer these questions for this sheet? What could make this sheet easier to read and use? What else would you recommend? Look at your printout of this sheet at the end of Project 2 [trips9.xls] and compare it to the current version. Is it easier to understand and work with now?

Record Analysis: Multiple lines in a cell

You will put your analysis on the sheet itself. To type multiple lines in a cell you must wrap the text. To start a new line inside a cell use the key combo ALT + ENTER. This is different from the key combo for a line break in Word. (SHIFT + ENTER) To get a wide area for your text, you can merge some cells.

  1. Select range A50:D50. (Suggestion: Use the Name Box.)
  2. From the menu select  Format  |  Cells…  |  Alignment 
  3. Check the boxes for Wrap text and for Merge Cells. Close the Format Cells dialog.
  4. In the Formula bar type  Analysis:  then press ALT + ENTER. This creates a line break inside the cell.
  5. Now type your answers to analysis questions 1, 2, 3, & 4. Be brief but complete. Use ALT + ENTER to create new lines.
    If your text is too long to fit inside the space, you can increase the row height by dragging the bottom edge of the row heading down. To make the space wider you can merge the merged cell with cells to the right. Don't widen your merged cell. That will change the width of the columns in the tables above.
  6. Resize row height as needed to show all of your text.Page Break Preview - page 2
  7. With cell A50 selected, from the menu select  Insert  |  Page Break . A page break is inserted above row 50.
  8. Open Page Break Preview and note that your analysis is on page 2.
  9. Spell Check cell A50.
  10. Page Setup: Correct the sheet's header to show Project 5. If necessary, on the Sheet tab in the Print titles box, type  $1:$2  so that the first two rows, with the titles, will print on all pages of this sheet.
  11. Class diskSave as  trips31.xls  to your Class disk in the folder excel project5. If necessary, create this folder.
  12. Print Print page 2 only by selecting  File  |  Print  and then Pages From 2 To 2 in the Print dialog.
  13. Print-out: Analysis only for trips31.xlsYour page will have your own text below the titles, which will make your page look different from the illustration.