Groups & Formulas:
Exercise Excel 4-1

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

You need to use what you just learned, and maybe learn a little more. Complete all parts of the following exercises. Don't forget to backup your Class disk when you have completed the exercises or whenever you stop for the day and saved a document along the way.

This exercise uses files from Project 3. Save the changed documents to your Class disk in the excel project4 folder. This keeps the original files intact in case you need to start over.
Full floppy disk How to handle a full disk

 


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 & Formulas
    SubtotalsTo subtopics
    Images & DrawingsTo subtopics
    Changes & FormulasTo subtopics
    Summary
    Quiz
    Exercises To subtopics
    Exercise Ex. 1 Amazon Pings
    Exercise Ex. 2 Theater Tickets
    Exercise Ex. 3 Computers Today
    Exercise Ex. 4 On Your Own

Project 5: DesignTo subtopics

Search 
Glossary  
Appendix

Exercise Excel 4-1:

Amazon Pings: Insert and Repair
What you will do: Copy and insert data
Print selection
Paste Link
Repair formulas
Copy formatting
Revise chart
Print grouped sheets

Start with: Class disk, amazon pings3.xls from Project 3: Ex. 3-3

Were your formulas in amazon pings3.xls correctly designed to automatically include inserted columns? You will find out now.

  1. Class disk Open amazon pings3.xls from the excel project3 folder of your Class disk.
     
  2. Save As with the name  amazon pings4.xls  to the folder excel project4.
     
  3. Select the sheet Week 2. Note the values in the Average, Maximum, and Minimum columns and the values in M31:M33, the cells that are supposed to use all the data cells. You will need these values later to see if they change.
     
  4. Insert Columns: Select on sheet Original Data the data for the third week (range Q1:W33) and Copy. On sheet Week 2 select cell J1 and Insert | Copied cells. Shift cells to right. This inserts the cells but they are not linked to the original data, so continue with Paste Special… | Paste Link. (If you can not paste, the Clipboard lost your copy. Copy again from Original Data.)

    Why this awkward method? Inserting first creates the columns to Paste Link in. If you try to link first, you lose the existing columns for Average, Maximum, and Minimum.
     
  5. Edit: Change cell G1 and the sheet tab to read Week 2 & 3. Remove the zeros from the cells that were originally blank. The data cells with zeros mean that the system was down at those times and no pings were recorded. Delete those zeros also since they are not real ping times. The Minimum time can not really be zero!
     
  6. Copy table formats: Select column I and Copy. Select columns J through Q and Paste Special… | Formats.
     
  7. Resize: Resize the new columns to 9.57 to match what AutoFormat did, if necessary.
     
  8. Formulas: Are all your formulas including the new cells? Ranges should go to column Q. Show the formulas. [Hint: Tools | Options] Revise if necessary.
     
    [If the totals were not updated, then your original ranges for the formulas did not include the blank column, originally Column J.]
     
  9. Chart: Edit the title for the chart to read  Week 2 & 3  and the sheet tab to read Chart - Weeks 2 & 3. Drag the updated chart wide enough to show all the dates along the bottom. (about as wide as columns A to L)

    [If the chart was not updated, then your original ranges for the chart did not include the blank column, Column J. Select the chart and from the menu choose Source Data…. | Series. Edit the ranges for Values for each Series and for Category (X) axis labels to end in column Q instead of column I.]
     
  10. Prepare to print: Edit the headers for the sheets to read Exercise Excel 4-1. Spell Check. Look at Page Break Preview and Print Preview. Select sheet Weeks 2 and 3, and under Page Setup | Page, set the orientation to Landscape and Fit to 1 page wide by 1 page tall. Select sheet Chart - Weeks 2 and 3,  and check under Page Setup | Page that it will print with Portrait orientation and Fit to 1 page wide by 1 page tall.
     
  11. Class diskSave.  [amazon pings4.xls] 
     
  12. Select the two sheets Week 2 and Chart.
     
  13. Print the active sheets. One will be landscape and one portrait. [If the chart is still selected when you try to print, the chart will take up a whole page.]
     
    After the printing is finished, close the workbook.
    Print-out for Exercise - sheet and chart