Creating Excel Charts

Click on the 'Lab Manual' for detailed instructions.

Create a new Excel workbook named hw9.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 column 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 by switching the rows and columns.
  • Save it on the same sheet.

  • Remove all the tick marks
  • Format the Y axis as seen in the image
  • 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 hw9.docx.
  • Title the Word document 'Farm Income 1997 - 2002'
  • Center the title on the page and make it 28 point font
  • Copy and paste the pie chart from Exercise 1 into the Word document
  • 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.
  • Copy and paste the second bar chart titled 'Annual Farm Income by Type of Income' to the word document
  • You will turn in this Word document along with the Excel spreadsheet.

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

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

Homework 9, 1234, Samantha Spade

(Replace Samantha Spade with your name and 1234 with the last four 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 instructor 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.