How to Make Graphs with Excel
by Prof. Jim Lengel, Boston University College of Communication
Numbers appear all around us. They trace the rise and fall of the stock market,
they record the progress of our students, they list the distances to the planets,
and they weigh the guinea pig in the back of the classroom. Our students spend
a good deal of their time in school learning to work with numbers, and to understand
how numbers help explain how the world works. Computers need numbers in order
to operate, and in fact in the early days of computing you needed to feed the
machine numbers - it did not understand text, nor images nor sound.
Collections of numbers, like the results of an election, or the batting averages
of a team, or the growth of a population over the years, are often used to communicate
important ideas in school and in public communication. But it takes a lot of
work on the part of the viewer to interpret these numbers to understand what
they mean. It's often more efficient to use a graph to show the "story
behind the numbers." And in most math curricula, the making and understanding
of graphs is an important skill. This week's article gets you and your students started making
graphs from collections of numbers.
The instructions here refer to Microsoft Excel, which is the program most people
use to organize their numbers and to create graphs quickly. Other spreadsheet
programs, such as AppleWorks, that might be found in a school operate in similar
ways and will require few modifications to the steps listed here. Feel free to copy these instructions for your students or modify them as needed for a particular assignment.
The best way to learn to make graphs with a spreadsheet is to build a small
collection of numbers, and then experiment with various graphs. So take the
browser window in which you are reading this article, and make it smaller, so
that it covers about half the screen to the right. Then open your spreadsheet
program and create a new blank document. Narrow this window toward the left of your screen
so that you can see both the spreadsheet and this article at the same time.
Into the spreadsheet enter the names of the members of a family (real or imaginary) and their heights.
Your collection of data should look something like this:
In this example, the numbers that represent height are called values,
and the words that represent names are called labels. Each label and
value is entered in one cell of the spreadsheet. The label Jimmy,
for instance, is in cell A3 - column A, row 3 - while the
value 73 is in B6. And cell C7 is empty. (Of course you may use any
kind of data you like for this exercise, but keep it small and simple to begin
with using short labels and straightforward values.)
Make a Graph
The process of making a graph from data like these takes four steps:
- Select some data. Click and drag the mouse to select the numbers and words
that you want to include in the graph. In this example we will graph all of
the data, but in many situations you won't want to include all the information
in the graph. Select only what you need, in this case selecting the area from
A1 to B6.
- Create a chart. From the menubar choose Insert - Chart. Then choose the
type of chart you want (column, bar, line, pie, and so forth). Then click
the Finish button. (Notice that Excel refers to what you are making as a chart,
while most teachers would call this a graph. Don't let the terms
- Examine your graph. Excel will construct your graph automatically, but not
always in exactly the way that you might wish. Does it appear as you had planned?
Does it include all the necessary information? Will it make sense to the audience?
Does it include any extraneous or distracting elements? Make a note of what
- Modify the graph. For a simple graph like this, you may not need to change
anything. But if you do, simply double-click the item you want to change,
and you will get a dialog box that lets you change it. To get rid of an item
on the graph, you simply select it (one click) and press the delete or backspace
Types of Graphs
For displaying the comparative heights of the members of a family, a column
graph is appropriate, because the vertical columns in the chart appear similar
to people standing up next to each other. If we were graphing the distances
to various cities, we might best show it as a horizontal bar graph, because
we move horizontally when we travel to them. A pie graph is best used when we
are showing the parts of a whole, such as the proportions of a population with
various colors of eyes. A line graph might best represent data that shows a continuous
trend or flow over time, such as air temperature.
To change a graph from one type to another, select the graph, and then from
the menubar choose Chart - Chart type... Notice that Excel provides two levels
of choice here, a general graph type on the left, and a specific format on the
right. The best way to understand how this works is to make a graph of your
own, and then try changing the type to see the results.
Avoid the tendency to make the graph too fancy. Excel provides many formats,
some of which are over decorated and make your graph hard to understand. Choose
the simpler formats and your graph will do better job communicating to its audience.
To make a more complex graph, you would add more columns of values to your
spreadsheet, and then show them all in one graph. For instance, in the example
shown above you might add a column for weight and another for girth. But be
careful - a graph that compares widely different measures is hard to make sense
of. In this case, you would avoid including a column that showed the annual
income of each person on the list, because the numbers would be way off the
scale and the comparisons with height and weight irrelevant.
To make a graph from a spreadsheet with multiple columns, carefully select
all the data that you need, and then from the menubar choose Insert - Graph.
All of the selected data will be shown in the graph, each column of numbers
with its own line or box.
Sometimes the values of the various measurements are out of proportion to each
other, such as heights that range from 50 to 70 inches next to weights that
range from 150 to 300 pounds. The graph produced by such data might not be easy
to interpret if both measurements are plotted on the same scale. Excel lets
you construct a graph with two different axes, each with its own scale of values.
To create such a graph, choose the Custom Types tab in the Chart Wizard
window, and then scroll down to the Line-Column on 2 axes type. This
will scale the heights from 0 to 80 on the left axis, and the weights from 0
to 300 on the right.
When you save an Excel document, the graph is saved with it, and whenever it
is opened the graph will appear where you left it. You can also save the table
of numbers and the graph as a web page, by choosing Save as Web Page from the
File menu. You can also copy the graph alone by selecting it, and then choosing
Copy from the Edit menu. From here it can be pasted into any document, such
as a report in Microsoft Word or an image in Adobe Photoshop.
To print the graph along with the table of numbers on the same page, first
deselect the graph (this is best done by clicking the mouse in one of the cells
of the spreadsheet), and then choose Print from the File menu. To print only
the graph, select it by clicking once on the graph, and then choose Print from
the File menu.
Students and teachers both can find many ways to use graphs for teaching and
learning. The best way to get started is to experiment with your own collections
of numbers, graphing them every chance you get, and trying different types and
formats until you find the ones that work best.
View Teaching with Technology Archive