Activity 2.3a - Graphs Using Excel

Purpose

It is often said that a picture is worth a thousand words. In the case of statistics, it isn’t easy to look at charts or lists of numbers to be able to tell trends, make comparisons or try to make generalizations. Histograms (column graphs), circle graphs (pie charts) and broken line graphs all create a visual representation of data.

Excel has built in functions to create such graphs. This tutorial will lead you through several examples to learn how to create histograms, circle graphs and broken line graphs using the features found in Excel.

Equipment

Excel program

Computer

Printer

Procedure

Excel is a Windows based spreadsheet program, with many powerful features. At first glance it is nothing more than a matrix (table) of rows and columns, called cells. Cells can contain words or numbers.

Fig.1 Excel Table

Each cell has a “name” associated with it.

“Winter” is in cell A5

“Fall” is in cell B3

“24” is in cell C1

“25” is in cell D2

In other words, the column letter and row number name each cell.

Fig.2 Excel Table, data in rows and columns

If information is placed in a continuous row or column, the designation is as follows:

The numbers are in cells B3, C3, D3, and E3 denoted by B3:E3.

The words are in cells A4, A5, A6 and A7 denoted by A4:A7.

  1. Creating a Circle Graph

Example problem: For the fiscal year of 1992, the government of GothamCity received the indicated amounts of revenue from the following sources:

Federal Aid $70,000,000

Licenses 10,000,000

State Aid 30,000,000

Sales Tax 20,000,000

Property Tax 60,000,000

Other 10,000,000

Create a circle graph.

  1. No matter what kind of graph you are trying to create, the first step is to always enter the information into cells in Excel. We are interested in the numbers, not the categories at this point. (Remember from your math class – a circle graph shows what percent of the whole, each amount represents.)
  1. In cells A1 though A6 enter the numbers listed above.

Fig.3 Values in A1:A6

  1. Highlight all the cells, and choose the icon for graphs. There is one of two ways to find it: One method is to go to Inset, the clicking on Chart (as indicated). The second way is to choose it straight off the tool bar.

Fig.4 Chart Icon

  1. Choose Pie and any one of the Chart Sub-types. For this exercise we are going to use the one on the top left (the one darkened in).

Fig.5 Chart Wizard – Step 1

  1. Click Next
  1. Step 2 appears. The first page of the second step is showing what data will be used in the circle graph. Nothing needs to be changed on this page.

Fig.6 Page 1 of Step 2

  1. Click on the series Tab.

Here we need to enter some information to make the graph more understandable.

  1. In the Name area: enter the Title of the Graph.
  1. In the Category Label Area, enter the categories for each of the numbers, in the same order as the numbers you entered. For example: Your first number in Excel is $70,000,000 – which represented Federal Aid. That should be the first entry, then Licenses, etc. Separate each entry with a comma.

Fig.7 Page 2 of Step 2

  1. Click Next. You are now at step 3: Chart Options.

Fig.8 Page 1 of step 3

There are three pages here, investigate them.

  1. On the third page, Data Labels, check off the label for Show percent. Notice what the preview looks like.

Fig.9 Page 3 of Step 3

  1. Click Next. Step 4 – Chart Location

Fig.10Step 4 – Chart Location

  1. This step is asking where to place the graph. Leave it as it is: as an object in Sheet 1.
  1. Click Finish. On the screen where you entered the data, you now see the graph.
  1. If you choose print from the pull down menu (File>Print), you will get a paper copy of thisgraph.

Creating a Histogram (Column Graph)

Example: The table below gives the results of a driver education experiment. The experiment measures the time between the appearance of a stimulus on a screen and a student’s reaction of depressing the brake pedal.

Time (to the nearest sec.) Frequency

.1 1

.2 4

.35

.4 4

.5 3

.6 1

.72

  1. Enter the Frequency into a column in Excel.
  1. Open , Chart Wizard, and choose column graph. As before, for this example we are going to choose the default graph (the one in black).

Fig.11 Column Graph

  1. Click Next
  1. Step 2 appears. As was the case with the circle graph, the first tab – DataRange confirms the data that we highlighted. On the second tab – Series we need to enter some information to label the graph.

Fig.12 Series tab on Step 2

  1. In the Name section, enter the name for this graph: Stopping Reaction Times
  1. In the category (x) axis labels, enter the labels for the values on the x axis, separated by commas: .1,.2,.3,.4,.5,.6,.7

Fig.13 Series tab of Step 2 with information entered

  1. Click Next. Step 3 – Chart Options

Titles Tab –

Enter the chart title: Stopping Reaction Times

Category (x) Axis: Time/Seconds

Category (y) Axis: Number of Students

(these are the labels for the chart)

Fig.14 Titles of Step 3

  1. Click on each of the other tabs. Make any changes you feel are necessary. If you don’t like it, then change it back. Notice that Excel always gives you a running preview of the work that you are doing.
  1. Click Next. Step 4 is asking where to place the graph. As with the circle graph, leave the graph as a part of the page where the information was created.
  1. Click Finish. Print the graph if you wish.

Creating Broken Line Graphs

Example: Create a broken line graph to describe any trends that might be evident.

Manatees are large gentle sea creatures that like along the Florida coast. Many manatees are killed or injured each year by powerboats. The table below gives data on powerboat registration (in thousands) and the number of manatees killed by boats in Florida in the years 1977 to 1990.

Year Boats (in thous) Manatees

1977 447 13

1978 46021

1979 481 24

1980 498 16

1981 513 24

1982 512 20

1983 526 15

1984 559 34

1985 585 33

1986 614 33

1987 645 39

1988 675 43

1989 711 50

1990 719 47

  1. Enter the information into the cells in Excel. This time enter the years in a column of its own.

Fig.15 Data in Cells

  1. Highlight the information
  1. Open , Chart Wizard, and choose XY (Scatter) graph.

Fig.16 Step 1

  1. Choose the type shown: Scatter with data points connected by lines
  1. Click Next.
  1. Choose the series tab.
  2. Click on Series 1 (in the left dialog box) and where it says name enter Boats (inthousands).
  3. Click on Series 2 (in the left dialog box) and where is says name enter Manatees Killed.Notice that the key near the graph changes.

Fig.17 Step 2 - Series tab

  1. Click Next

Fig.18 Step 3 – Chart Options

  1. Enter a title (Manatees and Boats), a label for the x axis (Years) and a label for the y axis (Number of animals)
  1. Change any of the information under the other tabs as you see fit.
  1. Click Next, then click Finish.

Fig.19 Final Graph

Conclusion

  1. Using a small package of multi-colored candies (like M&M’s), count the number of each color that is in the package. Create a Circle graph and a histogram of the information.

Project Lead The Way, Inc.

Copyright 2007

POE – Unit 2 – Activity 2.3a – Graphs Using Excel– Page 1