Format & Arrange:
Exercise Excel 3-3

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.

These exercises use files from the the Project 2. Save the changed documents to your Class disk in the excel project3 folder. This keeps the original files intact in case you need to start over or another student will be using this same computer.
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 & Arrange
    Format CellsTo subtopics
    Format ChartTo subtopics
    ArrangeTo subtopics
    Exercises Open arrow to subtopics
    Exercise" Ex. 1 Theater Tickets
    Exercise" Ex. 2 Soccer Budget
    Exercise" Ex. 3 Amazon Pings
    Exercise" Ex. 4 On Your Own

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Exercise Excel 3-3:

Amazon Pings -
Paste Link & Format

What you will do: Paste Link data
Format numbers with dialog
AutoFormat table
Create a line chart
Format the chart
Rename sheets
Scale to fit one sheet

Start with: Class disk, amazon pings2.xls (created in previous exercise)

The spreadsheet amazon pings2.xls [created in Exercise Excel 2-3] contains the records of a series of tests (over several weeks) of Internet speed to the site. Each test measures the time for a signal to make the round trip to and back. This signal is called a "ping".

You will create a sheet for the data for a single week, along with a chart of average, maximum, and minimum ping times for each day.

  1. Save From your Class disk open the file amazon pings2.xls that you saved in the excel project2 folder for Exercise Excel 2-3.
  2. Save Save As with the name  amazon pings3.xls  to the folder excel project3.
  3. Paste Link: In Normal view, copy the titles and labels in Column A and Paste Special… | Paste Link them to Sheet2 in cell A1. Copy the data for the second week - J1:P33 - and Paste Link to cell C1.
  4. Edit: Delete all the zeros that appear where there were blank cells originally.
  5. Format: Using Format Cells… | Number,
       Time- Format cells A6:A29 as time like 1:30:55 PM.
       Date- Format cells C4:I4 as dates like 03/04/97.
       Data- Format the data cells and calculated values as Number with 0 decimals.
  6. Enter data: In cell G1 type Week 2. In cell K3 type Average, in L3 type Maximum, in M3 type Minimum. In cell K31 type Average of all data:, in cell K32 type Maximum of all data:, in cell K33 type Minimum of all data:
  7. Formulas: Create formulas - in cell K6 to average the data in that row, in cell L6 to find the maximum value in the row, and in cell M6 to find the minimum value in the row. Copy these formulas down the column for each hour of the day.
    Write a formula in cell M31 for the average of all of the data cells, in cell M32 to find the maximum, and in cell M33 for the minimum.
  8. AutoFormat: Apply the table format Classic 2 to the entire table (but not the whole page!)
  9. Resize: Resize columns K, L, and M to 8.00 but be sure that the labels in K31:K33 show completely. Resize Columns J to 2.00 to help reduce the table's width.
  10. Chart: Select the dates across the top and the values at the bottom of those columns for Averages, Maximum, and Minimum. Use the Chart Wizard to create a Line chart with markers at each data point. On the Series tab, name Series 1 = Average, Series 2 = Maximum, Series 3 = Minimum. Set the title = Amazon Pings - Week 2 and the Value (Y) Axis = milliseconds. Place the chart as an object in Sheet 3. Drag the chart to the upper left of Sheet3.
  11. Format Chart: Format the Chart area with Fill Effect | Gradient using Light Turquoise and Aqua as the two colors. Select the Horizontal gradient that is light in the middle and darker at the top and bottom.
  12. Rename sheets: Change the name of Sheet1 to Original Data, Sheet2 to Week 2, Sheet3 to Chart.
  13. Prepare to print: Create or edit the header for each sheet to include your name and date in the Left section, file and sheet name in the Center section, and Exercise Excel 3-3 in the Right section. Spell Check. View Page Break Preview and/or Print Preview for sheets Week 2 and Chart - Week 2. Select the sheet Week 2 and set Page Setup | Page to print To fit 1 page wide by 1 page tall. Select sheet Chart - Week 2 and set Page Setup | Page to print at scaling = 100%.
  14. SaveSave. [amazon pings3.xls]
  15. Print Print: Group sheets Week 2 and Chart - Week 2 and print the two active sheets. Check the print preview to be sure your Page Setup choices are still there. You should have two pages only.

Print-outs for Exercise Excel 3-2 (2 sheets)