Creating Excel Charts

Click on the 'Lab Manual' for detailed instructions.

Create a new Excel workbook named hw8.xlsx.

In this assignment make your pie charts and bar charts look as similar as possible to the examples in the instructions. Don't worry about the exact coloring except for the one direction where it asks for a black bar in one of the bar charts.

Exercise 1

  • Name the first sheet of the workbook 'Exercise 1'.
  • Enter the following into the 'Exercise 1' worksheet:

      A                      B        C        D
    1 Gross Domestic Expenditure
    2 (in billions of dollars)
    4                          1990     1995     2003
    5 Personal Consumption   3831.5   4669.1   6824.9
    6 Investment              847.2   1110.7   1957.4
    7 Foreign Trade Deficit    71.4     84.2    206.8
    8 Government             1181.4   1372.1   2128.7

  • Create the pie chart below from the above data.
  • Save the chart on the sheet with the data.

Exercise 2

  • In the same workbook name a worksheet 'Exercise 2' and enter the data below.
  • Format the dollar amounts as Currency ($) with no decimal places.

      A               B       C       D        E        F       G
    1 Farm Income in the US
    2 (in billions of dollars)
    4                 1997    1998    1999     2000     2001    2002 
    6 Crops           101,000 106,200 111,100  102,200  95,100  93,300
    7 Livestock       87,100  93,000  96,500   94,500   96,600  96,500
    8 Govt. Payments  7,300   7,300   7,500    12,200   22,700  17,200

  • Create the bar chart shown below using the data from 'Exercise 2'.
  • Save the chart on the same sheet as the data.

  • This chart should be formatted as follows (it should look like the one above):
    • The title is bold and enlarged.
    • The legend does not have a border.
    • The scale of the value axis is 25,000.
    • The colors of the columns have been changed to brighter colors.
    • The fonts on both axes are made smaller.
    • The background of the plot area is changed to a fill effect.

  • Create another chart using the same data that looks like the one below.
  • Save it on the same sheet.

  • Remove all the tick marks
  • Change the background color of the title
  • Give the title a border of size 1
  • Change the background color of the plot area
  • Change the background color of the chart
  • Round the corners of the chart

Exercise 3

  • Open a Word document called hw8.docx.
  • Create a 'text box' on the word document for a title
  • Title the Word document 'Farm Income 1997 - 2002'
  • Center the title on the page and make it 28 point font
  • Copy and paste the TABLE from Exercise 2 into the Word document.
  • Center the table on the page.
  • Copy and paste the first bar chart titled 'Farm Income' below the table.
  • Center the chart on the page.
  • You will turn in this Word document along with the Excel spreadsheet.

Save the Excel workbook as 'hw8.xlsx' in the 'Excel Workbook' format (it should be default) and the Work document as 'hw8.docx' in the Word Document format (it should be default). Send both hw8.xlsx and hw8.docx as an email attachment to your TA.

In the subject line for your email, include the assignment number, your name and the last 3 digits of your RunnerID in this form:

Homework 8, 123, Samantha Spade

(Replace Samantha Spade with your name and 123 with the last three digits of your student ID). The subject line must be exactly in this format (including capital letters and commas) in order for my grading script to download your assignment correctly, so please follow these instructions carefully.

Errors in your homework grade must be worked out with your TA within one week of grades being posted. You will be asked to show the assignment in the sent email box with the attachment to the TA as proof that you submitted your work on time.

Remove your USB drive and take the drive with you when you leave. This should be the last action when you leave the lab every time. The lab computers are wiped clean every night and all data stored on them is deleted. USB drives left plugged into the computer usually find a new owner.