Kenyon College
Working With Charts in Excel
Have the data that you want to graph in an Excel spreadsheet. The exact arrangement in
rows or columns, and the location of the variables is not important. If there are missing values
that are denoted by a period “.”, or another symbol, convert those to blank cells.
Creating a Chart
To create a chart with Excel 2007, start by clicking INSERT in the top toolbar to “Insert”
at Chart. That opens a blank box for the chart. In the center at the top are pictures of the
standard types of charts – choose one by clicking on it, such as Line, Scatter, etc. Then select a
chart Sub-type from the pictures that come up. You can check different ones to make a decision,
and at any time, you can return to this step to change the chart type easily.
Click next on the SELECT DATA box at the top left, where you specify the data series.
ADD a data series or you can edit the ones that Excel selected, and you can remove series. To
remove a data series, click on the variable name in the box at the left, then press the REMOVE
button. You can come back to this step later to edit the data series. The variables that you
selected should appear in the series box. To add a different series, click ADD and then fill in the
name and values with the check boxes to select a range in the spreadsheet. With a LINE chart,
you can add a label for the X-axis with the Category (X) axis labels box. With a SCATTER
chart, X-axis is a variable. As you arrange the data sources, the chart appears in the box. When
you have the appearance as you want, click OK.
Click anywhere in the chart to make it active, and then a set of menus appears at the top
as CHART TOOLS. At LAYOUT, you can click Chart Titles to enter the overall title for the
chart. Click Axis Titles to name the axes, or AXIS to edit the appearance of the X or Y axis.
On other tabs you can add gridlines or remove them and add or remove numerical values from
the X and Y-axes. A Legend is automatically provided at the right side of the chart, and if you
have not given the variable a name, it will be “Series 1” etc. If the Legend is not useful, for
example if there is only one variable or if the lines are obvious, you can remove the legend from
the chart at Chart Options and Legend. Data Labels allow you to show either the x-axis or y-axis
values next to each data point. An alternative to this is to add XY Labeler to the Tools menu, on
your own computer but to available on Kenyon Lab computers, which will allow you to select
another variable to label each point. Finally you can attach the Data Table in spreadsheet form
to the chart.
At the DESIGN tab, there is a button for Chart Location at the right. That lets you move
the chart to its own worksheet rather than have it embedded in the data worksheet. You can
save it as an embedded chart in the spreadsheet itself, where you can manage the size. Or save it
as a new sheet with a Chart title. Choose one and then click OK.
Chart Types
1) Bar or Column
You can take the output of the Histogram procedure from Data Analysis and make a
chart from that, like the Chart Output results. First create the “bins” for the histogram - these are
the groups that will correspond to the bars in the chart. In a column, list the midpoints of several
value ranges that cover the range of the sample evenly. You specify how many ranges by the
number of bins you create, and you may need to experiment with that to yield a useful Histogram
- not too many since the purpose is to summarize, but enough to show the main pattern and
pattern of distribution. Too much aggregation will hide important patterns.
Click TOOLS and DATA ANALYSIS, then HISTOGRAM. In the dialog box, specify
the data range for the values of the variable you want to analyze, and then the bin range. If you
want a cumulative percent, check that box. Click Chart Output to have Excel make the chart
automatically. Click OK, and that produces a table that lists the count of observations that fall
within the bin, the frequency, and the Chart. You can also create a column that has relative
frequency, the percent of observations in that bin.
Click Chart Wizard and Column chart. Put the count or relative frequency from the
Histogram results in the variable box and use the bins as the Category (X) labels. Follow
through the remaining steps of Chart Wizard to label the chart and save it. The result is a column
graph where the height of the columns indicates the count or relative frequency of observations
in evenly spaced groups.
The HISTOGRAM option at DATA ANALYSIS allows you to create a Chart Output for
the frequency table. It will not allow you to chart two or more variables together. For that you
can use HISTOGRAM on each variable individually to create the frequency table, and then use
and Excel Chart to create a Histogram for two or more variables together.
2) Line
Choose a Line chart for time series data. You can chart several data series on the y-axis
against a label, such as the year or other marker of time, on the x-axis. The x-axis is entered in
the Chart Source Data step as the Category (X) label. This is especially useful for monthly or
quarterly dates which might have the form: 1999:12 or 2003 Q2. If these are chosen as the X
variable in another type of chart, the months or quarters for each year will be condensed. The
Category (X) treats these as names or labels for each observation rather than values of a variable,
so you can’t use them for numerical calculations.
If the data proceed in a definite time pattern, you might want to choose a chart sub-type
that is a connected line to emphasize the pattern over time. If the data oscillate or change
dramatically back and forth, the connecting lines will crowd the chart and obscure the pattern.
Decide whether the time sequence matters for what you are presenting in the chart.
3) XY Scatter
When association, such as a correlation, is important, the XY Scatter chart is useful. If
you select the data before opening Chart Wizard, make sure that the variable you want on the x-
axis is in the column on the left and the variable for the y-axis is adjacent on the right. If you
open Chart Wizard first, you can have the data in any order as you will select each variable
separately. The Name box refers to the variable to be plotted on the y-axis, and then the Chart
Source Data step, Series tab, allows you to select ranges for the X values on the X-axis and the
variable values for the Y-axis. These are variables with numerical values, so you can use them
for a calculation, such as fitting a trend line.
Again you may just want a scatter diagram of dots if the relation between x and y is most
important and independent of the sequence of observations. If the sequence or ordering of the
observations is meaningful, you can select a chart sub-type that connects the points with a line.
Editing a Chart
After the chart is constructed, you can edit most aspects of it. Click in a blank area
within the chart to activate the edit mode. When you do, the toolbar at the top will add a menu
for Chart and you can either work from that or directly from the chart. Double-click a feature of
the chart, such as the x or y-axis line, and it will open the relevant dialog box so that you can
change the features.
Format Axis - allows you to change the scale, and set minimum or maximum values on
the axis, the y-intercept, choose a logarithmic scale, change the patterns of lines used for the
series, or the shape and size of the marker for each series. You can edit the font of the text that
labels the axis, the color or the orientation of the words. You can change the format of the
numbers to control the decimal places shown or use a currency format.
Format Data Series - double click on the line for any one variable. You can control the
color or shape and thickness of the line, size and shape of the marker, or whether labels are
shown (the x or y values). Error bars allows you to add several types of “confidence intervals”
and control the width.
Format Plot Area - double click on the background within the chart. You can edit the
border of the chart or the color and fill patterns of the chart background.
Format Legend - double click in the Legend box. You can control the pattern, color and
placement of the legends for each data series.
Identifying a data point - if you place the cursor on a point in the chart, Excel will display
the x and y values for that point.
Trend Line - place the cursor on the data series anywhere. Right click the mouse and that
shows a pop-up menu. One item is Trend line, and click on that. There are several options for a
linear trend line or non-linear trend lines that adjust to the data, and for including the estimated
equation in the Chart. Select one and click OK, and that will add a trend line into the chart, and
provide emphasis of an overall pattern.
Arrows - you can add arrows, lines, text or other markers. A toolbar for Drawing has an
icon for an arrow. Click on the arrow so the button is pressed, then move the cursor to the chart
where you want the tail of the arrow to begin. Press the mouse button and drag to where you
want the arrow to point, and release. That will insert and arrow to identify a particular place in
the chart.
Adding or removing data – click on the Chart drop-down menu in the toolbar (or right
click on the chart). Click Source Data, and then on the Series tab. As when you created the
chart, you can remove a data series, or add a data series.
Editing the Source Data – the chart is based on data that are entered in the Excel
spreadsheet, and linked to those data. If you change the value in a cell, or a column, the chart is
automatically updated to reflect the change. So if you made a mistake in entering data, or you
want to edit the data for another reason, you do not have to start over with the chart.