Charts
Definitions
Recall a few basic definitions.
Data Series - A group of related data points plotted in a chart
that originate from rows or columns on a single worksheet. Each
data series in a chart has a unique color or pattern. You can plot one
or more data series in a chart.
Axis - A line that borders one side of the plot area, providing
a frame of reference for measurement or comparison in a chart.
Legend - A box that identifies the patterns or colors
assigned to the data series or categories in a chart.
Some types of charts
There are a few major types of charts that we will deal with.
The Line Chart
A line chart shows trends in data at equal
intervals. This is especially useful to plot data that changes over time.
The XY Chart
An XY chart either shows the relationships
among the numeric values in several data series or plots two groups
of numbers as one series of xy coordinates.
The Pie Chart
A pie chart shows the proportional size of items
that make up a data series to the sum of the items. A pie chart has only
one data series.
The Bar Chart
A bar chart illustrates comparisons among
individual items. Categories are organized vertically, values horizontally,
to focus on comparing values and to place less emphasis on time.
The Stacked Bar Chart
A stacked bar chart shows the relationship
of individual items to the whole.
Creating charts
In order to create a chart we use the Chart Wizard. The icon for the
Chart Wizard is located on the Standard Toolbar.
The Chart Wizard contains a series of four steps to create a chart. At
each of these steps you can configure the appearance of your chart. At each
stage of the Chart Wizard, you are allowed to stop and accept Excel
defaults. You may do this by clicking the Finish button. By clicking the
Next > button, you will be taken to the next step of the Chart Wizard.
The first question you may answer is what type of chart you want
to create.
If you select each type of chart, then to the right you will
see sample charts.
If you select one of them, then you will see a short
description about the chart.
If you select and hold the button labeled
Press and hold to view sample, then you will see a sample
of how the data ranges you have selected will appear in the selected chart.
Notice at this point like in the first three questions of the Chart
Wizard, you have three options.
- You can make a choice, and then click the
button labeled Next >, and you will be taken to the next
question in the Chart Wizard.
- You can just simply click the button
labeled Next >, and the Excel default answer to the current
question will be accepted, and you will be taken to the next question
in the Chart Wizard.
- You can also click the button labeled Finish,
and this will allow you to accept the Excel defaults for all subsequent
questions in the Chart Wizard, and the chart will be drawn.
In all questions except the first, you also have the option of clicking the
button labeled < Back. This will take you to the previous
question in the Chart Wizard.
The second question you may answer is what data series you want to
use as the source for the chart.
The next question you may answer is about Chart Options. Here you
can make choices about the Chart Title, Axes, Gridlines, Legend, Data
Labels, and Data Table.
The last question you may answer is about Chart Location.
To draw the chart, click the button labeled Finish.
Sample Spreadsheets to use to draw charts
Line Chart
XY Chart
Pie Chart
Bar Chart and Stacked Bar Chart
Creating a default chart without going through the Chart
Wizard
In order to draw a chart without going through the Chart Wizard, you
may use the F11 key. After you have selected your data ranges, if you
hit the F11 key, then a default chart will be drawn.
The default chart type is a column chart. If you draw a particular
kind of chart frequently, then it might be useful to you to set that
chart type as the default. To do that, simply draw any chart, and then
right click inside the chart, and you will get a quick menu. One of
the options is Chart Type.... If you select this option, then you will
see the following dialog box.
To change the default chart type, choose the type of chart you want to
be the default, and then click the button labeled Set as
default chart.
Modifying a Chart
There are many ways to modify a chart. We will discuss a few of them.
- Changing the Chart Title
You can either change the Chart Title in the
Chart Wizard, or you can change it after you draw the Chart by right
clicking in an open area of the chart to the quickmenu. Choose Chart Options....
- Changing the size of a Chart
To change the size of a chart, move the mouse
pointer inside the chart and click the left mouse button until you see
the sizing handles around the chart. You may change the size by moving
the mouse pointer to one of the sizing handles until the mouse pointer
turns into a double-sided arrow. Then you may hold down the left mouse
button and drag the mouse until the chart is the desired size.
- Changing the location of a Chart
To change the location of a chart, move the
mouse pointer to an open area of the chart and hold down the left mouse
button until it turns into a four-sided arrow. (You may have to move
the mouse pointer a little.) You may then move the chart to the
desired location.
- Deleting a Chart
In order to delete a chart, move the mouse
pointer inside the chart and click the left mouse button until the
sizing handles appear around the chart. Then click the Delete key.
An example of Object Linking
Recall that Object Linking means that we place a reference to our
source
data in the destination. That is, if we wish for a Microsoft Word document
to contain a chart we created in Excel, and simultaneously we want to use
that chart in for instance a PowerPoint demonstration, we would place a
link to the chart we create in Excel. Then any change that we make to the
chart in Excel will also happen in Microsoft Word and Powerpoint
simultanesouly.
Note that this is much different that Object Embedding in which we
simply
make a copy of our source object and embed that in our destination.
You may use this sample data to see the effects
of
Object Linking.
- Draw the chart in Microsoft Excel
- Open a new Microsoft Word document and type the following:
"This is the sine function.".
- Add a few blank lines in the document by hitting the Enter key.
- Go back to Microsoft Excel and select the chart you have drawn. (That
is, click inside the chart until the sizing handles appear around the
chart.)
- Go to the Edit Menu and choose Copy. This will place the Chart Object
on the clipboard.
- Go to the Microsoft Word document.
- Go to the Edit Menu and choose Paste Special.
- In the dialog box that appears choose Paste link.
- Click OK.
- Go back to Microsoft Excel. Change any of the data in the data
ranges you used to draw the chart. Notice that not only will the change
take place in the chart you drew, but it will also take place in the
Microsoft Word document.
A Sample Spreadsheet
The following example was born out a question that is commonly asked
near the end of a quarter or semester: "What do I need to make on
the Final Exam in order to get an A for the course?"
Recall the way your final grade is calculated.
| Exam 1 |
25 % |
| Exam 2 |
25 % |
| Project 1 |
7.5 % |
| Project 2 |
7.5 % |
| Final Exam |
35 % |
If you receive a score of 100 on all exams and projects, then your
final course grade will be 100. Since you have your scores on the
first two exams and project 1, you can calculate a portion of your
grade.
Exercise
You may take 20 minutes to work on this exercise. You may work in groups of
two. Those who finish the exercise may earn up to 2 additional points
to their Spreadsheet Project grade.
The requirement of the exercise is to develop a template which will
allow a person to input the grades they have made so far, and determine
what they need to make on the Final Exam in order to achieve an A, B,
C, or D for the course. Recall the grading scale.
| 90 <= x < 100 |
A |
| 80 <= x < 90 |
B |
| 70 <= x < 80 |
C |
| 60 <= x < 70 |
D |
Consider the following example. Suppose Fred has made the following
scores this semester.
| Exam 1 |
100 |
| Exam 2 |
70 |
| Project 1 |
60 |
| Project 2 |
80 |
So he has accumulated 100*0.25+70*0.25+60*0.075+80*0.075=53 points.
Notice
that the total possible points for the exams and projects is 65.
Now Fred
would like to know what he would need to make on the Final Exam in
order to receive 90 points or better (an A).
So we take the total number
of points required for an A, 90 points, and substract the total
he has from all exams and projects except the Final Exam, 53.
So he needs
37 points out of the Final Exam in order to recieve an A for the course.
Notice that the number of points Fred will get from his Final Exam
grade is determined by multiplying the score he gets on the exam by
0.35.
So we have Score * 0.35 =37. So Score = 37/0.35 = 105.7143.
So
Fred would need to score between 105 and 106 on the Final Exam in order
to make an A for the course.
In order to receive the points, you must make
a print out of your template with formulas displayed
a print out showing your calculations for what a student needs to
make on the Final Exam in order to recieve an A, B, C, or D for the course.
Make sure to print the names of both members of your team on the printouts.
Notice that you can use your work to determine what you need to make on
the Final Exam in order to receive the grade you want for the course.