ENV* K105

Assignment #6—Charts and Graphs
Due 10/11/05

In this assignment you will learn about Charts and Formatting. Charts and graphs are covered in Chapter 6 of A Guide to Microsoft Excel 2002, Third Edition, (Liengme). You should read that chapter. You don't have to turn in any of the exercises, but you should try at least Exercises 1, 2, and 4.

1. Import into Excel the data in the Word documents carbon.doc and temperature.doc as follows:
1. Convert and import the data in carbon.doc:
1. Convert the document data:
• Save carbon.doc to your I:\ drive. Click here if you need information on saving a Word document from the Web.
• Open the file in Word.
• Delete the information which is not data (ie, the title and the source). Copy the title to the clipboard, then select the top row and choose Table, Delete>, Row. Highlight the paragraph below the table and hit delete. Leave the column labels (Year, etc.).
• Select the table by clicking anywhere in the taable and choosing Table, Select>, Table. Then convert the table to text by choosing Table, Convert>, Table to Text…. Select Tab as the delimiter.
• Save the file as a text only file. To do this choose Save as... and change the Save as type to Plain Text (*.txt). Be sure that you are in the folder where you want to save the file then click OK to accept the name carbon.txt. Click OK on the file conversion dialog.
• Close the file.
2. Import the data to Excel
• Under File, Open… choose "Files of type: Text Files."
• Select carbon.txt. Click Open.
• The file type is given as delimited. Choose Next>.
• Keep Tab as the delimiter, choose Next>. Apply any desired formatting to each column. Choose Finish.
3. Add the title and source back to your worksheet. Insert a row at the top of the worksheet. Paste the title in cell A1 and center it across all the columns that are in use. Put the source in a text box below the data.
2. Repeat for temperature.doc. Add this data as a second worksheet in the same workbook as the carbon data.
2. Format the carbon data as follows:
1. Set all the data and text to Arial font (including the text box).
2. The title and column headings bold and in 11 pt. The numbers in 10 pt.
3. Columns A, B, D, E, and F 8.57 wide; column C 9.43 wide.
4. Column headings and title centered and word-wrapped.
5. Borders around all the cells. The table should now look like this.
6. Freeze the panes on the carbon worksheet so that the column headings and the year are always visible.
• Highlight the cell immediately to the right and below the column and row you want to freeze.
• Select Window, Freeze Panes
7. Add a header ("Custom Header" under Page Setup…, Header/Footer) with your name left aligned, "Assignment Six" centered, and the date right aligned. Add a footer with "Three Rivers" right aligned. Print the worksheet on a single page.
3. Save the worksheet to your I: drive.
4. Create the following charts. Note: You may wish to move or otherwise manipulate the data you have imported. Save the charts and data in a new workbook on your I: drive.
1. Graph carbon emissions and temperature versus year using an x-y plot.
1. Insert a blank worksheet. Copy the appropriate data onto it as follows: Copy the title to A1. Add headings (Year, World Emissions, Global Temperature, and 10 Year Running Average) to cells A2:D2. Copy year data to column A, world emissions to column B, and global temperature to column C. Row three will have 1950 in A3; 1,620 in B3; and 14.86 in C3.
2. Create a fourth column with a ten-year running average of temperature for the years 1959 to 1993. Note: The running average for 1959 will be the average of 1950 through 1959; for 1960 it will be the average of 1951-1960; etc. There will be no values for the first nine years (D3:D11 will be blank).
3. Highlight the range you want to graph (ie, A2:D46) and use the chart wizard (Insert, Chart...) to create an x-y graph with smoothed lines. Include a chart title and x- and y-axis titles ("Year" and "million tons C"). Create the chart to a new sheet. Your temperature data will be a barely visible line at the bottom of the chart. You can fix that next.
4. Select the series you want to work on by clicking on its line on the chart. Right click and choose Format Data Series…
• For "Global Temperature," under the Axis tab, choose "Plot series on: Secondary Axis," and click Ok.
• For "Ten Year Running Average," under the Axis tab, choose "Plot series on: Secondary Axis"; AND under the Pattern tab choose Marker, None. Click Ok.
• Right click on a blank area of the chart and select "Chart Options…." Under Titles, add "degrees Centigrade" to the Second value (Y) axis box. Click Ok.
• Right click the right hand y-axis. Choose Format Axis…. Under the Scale tab, set the maximum value to 15.5. Click Ok. Set the minimum value for the left hand y-axis (carbon emissions) to 1000.
5. Your finished chart should look like the one in http://environmentalet.org/env105/globalwarming.htm.
2. Create a stacked column graph with year on the x-axis and carbon emissions from (1) the industrial nations, (2) the eastern bloc nations and (3) the other nations as your y-values.
1. Copy the appropriate data, with column headings, to a blank sheet.
2. Select the three columns of emission values (ie, don’t include the years) before clicking the chart wizard.
3. In step 2 of 4, select the Series tab. Go to the "Category (x) axis labels:" box and select the range that has the years.
4. Add a chart title ("Carbon Emissions by Region[shift enter]data from Worldwatch Institute") and axis labels ("Million Tons C per Year" and "Year").
5. Format the chart title so that the first line is Arial Bold 16 pt and the second line is Arial Regular 11 pt.
6. Your finished chart should look like the one in http://environmentalet.org/env105/columngraph.htm.
5. Print your charts with a header and footer as above, and print the table from part 1 above.
6. Use WebCT to send me all your files. Please include your initials somewhere in the name of each file you turn in.