Exercise Excel 3-3:
Amazon Pings -
Paste Link & Format
|What you will do:
||Paste Link data
Format numbers with dialog
Create a line chart
Format the chart
Scale to fit one sheet
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
Amazon.com site. Each test measures the time for a signal to make the
round trip to amazon.com 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.
From your Class disk open the file amazon pings2.xls
that you saved in the
project2 folder for
Exercise Excel 2-3.
Save As with the name amazon pings3.xls
to the folder excel project3.
- 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.
- Edit: Delete all the zeros that appear where there were blank cells
- 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.
- 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:
- 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.
- AutoFormat: Apply the table format Classic 2 to the entire table
(but not the whole page!)
- 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.
- 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.
- 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.
- Rename sheets: Change the name of Sheet1 to Original Data, Sheet2 to
Week 2, Sheet3 to Chart.
- 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
Page to print at scaling = 100%.
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.