Creating a Chart in Excel

2007

Reference Handout
Introduction

The purpose of this class is to teach the end user a more effective way to communicate
data by the use of charts via Excel. We will introduce the basic uses of a chart as well
as some advanced features. To begin, we will start with the basics of setting up a
spread sheet for the use of a
chart. Next, we will work on
some sample charts followed
by a project to be completed
based on using the guidelines
from this course.

Notes on Visual Data

Graphics are used to reveal
data and paint a clear pic-
ture to the person reading
the data. As with any visual communication, keep these basic principles in mind when
creating a chart.

1. There are many types of charts, so the goal is to use the most effective style that
will tell a story about your data.

2. What is the data trying to say? Don’t

complicate the data with excess information,
color, or graphics. Keep the focus on what
the data is trying to say and avoid distorting
the data.

3. Encourage the viewer’s eye to compare

different pieces of the data.

4. Display the variation of data, not a varia-

tion of design.

Finally, when creating a chart, your chart should be able to answer this very simple
question: ”As compared to what?”
Getting Started

The key to effective charts is setting up a spreadsheet correctly. The data needs to be
arranged in such a way that will allow for logical and clear visual representation of the
data. For example in the figure below, the data is arranged by calendar year from left
to right with the data sets being arranged from top to bottom. Excel can translate the
worksheet data to a visual display by creating the calendar year on the the X-Axis and
the comparitive data in on the
Y-Axis. Most chart formats in
Excel are based on this prin-
ciple. When answering the
question, “As compared to
what”? Excel is comparing two
types of data based on the
the X and Y axis. An example
would be comparing time on
the X-Axis and percentage of
“X value” on the Y-Axis.

Excel 2007 Graph Menu’s

The new layout of menus within Excel 2007 has changed dramaticly since Excel 2003.
The 2007 version has incorporated a dynamic menu system that changes depending on
what tab is selected. This visual layout makes it easier to work within Excel. To begin
graphing in Excel 2007, select the Insert tab. From here look at the Chart section of the

menu. This menu is the starting off point for all charts in Excel. Selecting the data in
the worksheet, then select Insert, and then select the style of chart desired. Once the
chart style is selected Excel produces the chart in a small window within the worksheet.

The menu changes when the chart is selected to show Chart tools and underneath that
are 3 tabs, design, layout, and format.
The design menu is organized to allow the user to change elements within the selected
chart. Starting from left to right, this menu allows the user to change the type of chart,
save custom created charts as templates, select and modify data that the chart is utiliz-
ing, change generic chart layouts, select color styles, and move a chart from within the
workbook.

Chart Type - Utilize this command to change the type of chart that is currently displayed.
For example changing a pie chart to a stacked column chart.

Data - This command will allow the user to select and edit the data series from the
worksheet from which the chart is based. Switch Row/Column is a command the user
to switch the series from row to columns, which changes the way the visual data is rep-
resented. For example, if time was represented on the Y-Axis it would then be switched
and to the X-Axis.

Chart Layouts - This option turns the chart’s layout into one of several pre-formated
layouts. These chart items include the legend, axis labels, and data labels.

Chart Styles - Pre-formated color schemes for the selected chart.

Location - Moves the chart to anywhere within the workbook. Use this command to
move a chart of the default location to its own tab within the workbook.

The Layout menu allows users to modify specific areas of the chart such as the chart
title, axis titles, the chart legend, data labels, axes information, and format selected
elements of the chart via a drop down menu. Utilize the layout menu when placing the
finishing touches to a chart.
Current Selection- This provides a drop menu that will allow the user to format any ele-
ment of the chart. Formating elements of the chart can also be done by selecting the
chart element and selecting the right mouse button and choosing format “_____”.

Insert- This command allows the user to insert shapes (lines, connectors), text box, and
pictures into the chart.

Labels- This menu provides all the options available for creating labels within the chart.
Use this menu for adding and taking away legends, axis labeling, and chart titles.

Axes- Options for the x and y axis are found in this menu. Preformated options are
available using this menu.

Background- This menu is for the formatting of 3D styled charts. The plot area can also
be turned on and off from this
menu.

Analysis- From this menu the

user can add tredlines of various
types, lines, up and down bars,
and error bars. Lines added from
this menu will add drop lines and
high/low lines.

Making IT tell the Story

As stated earlier,
when presenting a
chart make sure that
it tells the story in a
complete and simple
fashion. To create a
chart select the data
by dragging the mouse
over the worksheet
data. Select Insert,
then choose the
stacked column
chart. The chart will
show up in the data
worksheet and the
screen should look like
the figure to the right.
Once the chart is made, we need to remove some of the automatic formatting
to simplify the chart. Make sure the chart is selected so the Chart Tools menu is
displayed. From the
design menu, choose
select data. This will
open a dialog box
that displays the chart
data. From this dialog
box the user can edit,
delete, or add data
as well as change the
order of the data on
the chart. For this
exercise remove the
data labeled “Number
of Labs Inspected.
The next step is to
re-arrange the data.
This can be done in
the same dialog box.
Arrange the data in
this order:
1. Labs not inspected
2. Inspected no
violations
3. Labs Inspected w/ one or more violations

After this is completed the chart should look like this

Now its time to put the polish on this chart. The next few steps are going to dramatical-
ly change the way the chart looks. The end result is a chart that effectively displays the
data to convey the message. The first step is to change the color of the data bars that
are found in the chart. This can be done in two ways.

The first way is to choose the preformated choice that are found under Design menu.
Remember that the chart must be selected to see the design menu. The second option
is to select the data series and right click to show
the menu. In that menu select format data point
at the bottom of the menu. This will open a dia-
log window like the one on the right. Under the
fill menu select solid fill which brings up the paint
bucket icon. Select the icon and that will bring up
the color options. Change all the color currently in
the chart. Select a color combination that is ap-
pealing to the eye, but most importantly relevant
to the data

After the color has been changed, remove the au-

tomatically formatted legend from the chart. To do
this select the layout menu and select legend from
the menu. Select none from the drop down menu.
Next remove the gridlines from the chart. This
option can be found in the layout menu as well.
Choose gridlines from the menu. From the drop
menu select primary horizontal gridlines and select
none.
When the changes are complete the graph should look something like this:

The last thing that needs to be done to the chart is provide relevant labeling. This is
signaficantly easier to do in
Excel 07 verses Excel 03. All
drawing, labeling and text
boxes can be found in the
layout menu. Select the
chart title to insert a title for
the chart. This command
will insert a text box at the
top of the chart. Add a title within the text box. Next add labeling for the chart axes.
To do this select the axis titles button within the layout menu. This drop down menu
will insert a label on either axis. Add a label for both axes. The final labeling needs
to identify the chart data since the legend has been removed. Using the text box
command and the shapes command to identify each data series. The shapes command
will draw preformated shapes with in the chart. This is useful for drawing arrows and
connector lines which is used for linking data and the informative text labels. When this
is completed the chart should look like the chart on the following page.
Practical Application

In a case study, the Office of Compliance sought to inform senior management each
source of compliance reports it received from 2006 and 2007. In preparing a report,
you have been enlisted to create a chart that would visually tell the story and clearly il-
lustrate to management this information.

FY 2006 FY 2007
Compliance Hotline 18 12
Directly to 48 37
Compliance Personnel
HR Exit Interview 13 5

Once the chart is complete compare with others.