A Quick Tour of Other Charst


0) Introduction:
1) A Tour of Other Charts:
2) Trendlines:
3) Summary:


0) Introduction:

In this lecture, we will examine and briefly describe other types of charts
that are available in Excel. We will see that we can easily change the type
of chart to find one that best presents the data.

Edward Tufte has written many books on the art of presenting information
(search and read about these titles on Amazon): "The Visual Display of
Quantitative Information", "Envisioning Information", "Beautiful Evidence",
and "Visual Explanations: Images and Quantities, Evidence and Narrative".
There is truly an art (and evolving science) for presenting information so
that it can be easily understood. Of course, one can also use "bad" graphics
to present information but hide what is important.

Tufte effusively praises Charles Joseph Minnard's graphic detailing the
crushing loses by Napoleon's army when it fought a campaign in Russian in
1812. See 

  http://en.wikipedia.org/wiki/Charles_Joseph_Minard

For a contrarian view of this graphic, see

   http://charts.jorgecamoes.com/minard-tufte-kosslyn-godin-napoleon/

I will not be teaching you the appropriate use of charts in presentations, but
in this lecture we will examine many of the easy-to-use charts available for
displaying data in Excel. Once the data is there, it is still up to us to
determine the best way to show it (including which order the data series are
presented in the legend). This process requires thinking and maybe
experimenting to try to find a good presentation.


1) A Tour of Other Charts:

We will examine a variety of charts here, mostly the main features and a few
of the most useful options for each.

We can create a chart by clicking the Chart Type (or the Other Charts
disclosure triangle) on the Charts group of the Insert tab.

We can change the type of a chart by selecting chart, then selecting the type
and subtype of the chart after clicking the Change Chart Type icon in the Type
group on the Design tab (visible whenever a chart is selected).


Pie Charts

We will start with the Pie chart, which is useful for comparing the relative
proportion of a bunch of quantities. On the Charts worksheet for this lecture,
I show grading data for a hypothetical class. Column A is the category
labels (the grades), and Column B is the number of students who received each
grades.

We can select these two columns and create a Pie chart with each category label
shown as a different slice in the chart. We can right-click the Pie chart and
click Format Data Series... and click Series Options to determine the Angle of
the first slice (controlled by a scrollbar: e.g., 0 means the first series
starts at the top, going  clockwise; 90 means the first series starts at the
X-axis, going clockwise). The other main option there, the Pie Explosion
scrollbar, allows us to separate the slices in space. In this chart I also
choose the Border Color (separating the pie slices) to be black and Border
Style. to be a .25 line.

Note that if we right-click the Pie chart and click Select Data..., if we
select Series1 and click Edit, we can specify the Series name (if we do so
before titling the chart, it becomes the Chart title) and the Series values.
To specify the As, Bs, ... Fs (done automatically when we created the chart
from the information in column A), we click Edit under Horizontal (Category)
Axis Labels.

After right-clicking the Pie chart we can also click Format Data Labels: along
with the standard ones (Category Name and Value) there is Percentage check box.
In the chart on the worksheet, I clicked only this checkbox and specified the
Number option as Percentage with 0 decimal places. Each number summarizes the
percentage of the pie that it occupies.

If when selecting the columns to chart we include the labels at the top,
Excel will use the label in Column A to title the chart and choose the other
two labels as the names for the data series.
 
-->Open the the workbook distributed with this lecture.
-->On the Charts worksheet, replicate the formatting of the Pie chart for
-->  grades



Doughnut Charts

While Pie charts nicely show one series of data, the Doughnut chart can show
more than one related series, so they can be easily compared. It displays each
series as slices (or rings) through the doughnut; multple series are shown as
concentric rings. On the Charts worksheet for this lecture, I show grading data
for a hypothetical class. The Column A are the category labels, and Column B is
the number of students who received each grade at Midterm, and Column C is the
number of students who received that Final grade.

We can select these three columns and create a Doughnut chart with each
category label a different ring in the chart. We can set the angle and
explosion as described above (as well as the size of the donught hole). In the
Doughnut chart I created labels for each percentage and used .25 black lines as
border (as described above: each percentage is shown inside the ring). 

I also set Series1 to be Midterm and Series2 to be Final, and selected (by
double-clicking then right-clicking) just the first data point for each series,
and formatting that data label to include the newly chosen series names
("Midterm" and "Final"). After selecting one point and right-clicking the
options will be singular, not plural: Format Data Point/Format Data Label.

If when selecting the columns to chart we include the labels at the top,
Excel will use the the labels in columns B and C as the names for these data
series.
 
-->On the Charts worksheet, replicate the formatting of the Doughnut chart for
-->  midterm and final grades.


Radar Charts

A third but similar way to view date like this is a Radar chart, which allows
us to visualize multiple related data series. In the Radar Chart, each category
label (As, Bs, etc.) has its own axis, and the values are plotted on each
axis, going clockwise. Again, we can select these three columns and create a
Radar chart with each category label a different axis in the chart (the second
subtype shows the data marks). Notice that in Radar charts we cannot use
percentages (of course we can calculate precentages on the spreadsheet and do
a Radar Chart of that data) to label the values (and even showing data labels
is cramped: better to "read their values" of the axis).

If when selecting the columns to chart we include the labels at the top,
Excel will use the the labels in columns B and C as the names for these data
series.
 
-->On the Charts worksheet, replicate the formatting of the Radar chart for
-->  midterm and final grades.



Column Charts

Next we move on to Column and Bar charts that display information equivalently:
The Column chart displays its categories horizontally and its values
vertically; the Bar chart displays its categories vertically and its values
horizontally. If the category names appear in the chart, and are short, either
can be used; if the category names are long, the Bar char allows more room to
display them. The example on the Charts worksheet shows sales of products
domestically and foreign, and the total sales month by month. These charts
frequently occur in business.

We can select these four columns (with their labels on top) and create a
Column chart with each category label associated with the three sales values
(domestic, foreign, and total). I annotated the axes with "Sales" and "Month".
I also included the data for this chart as part of the chart itself (we can do
this with Column, Bar, Line, and Area charts). To do this, select the chart,
then click the Data Table disclosure in the Labels group on the Layout tab, 
and final click either Show Data Table or Show Data Table with Legend Keys.
Sometimes we print charts on their own page, and including all the data in
text form on the chart is useful.

There are two other useful ways to control the format of this kind of chart.
By clicking any data series and clicking Format Data Series... we can squeeze
all the bars together by increasing the Overlap (0 overlap means they appear
side by side with no overlap); we can even use NEGATIVE overlap to put space
among the bars in each series. We can also control the Gap Width between each
grouped series.  As the Gap Width decreases, each of the bars grows wider (it
can decrease to 0) and as it increases, the bars widths' decrease. Note that
we can use the sliders to contol these values, or enter percents in boxes
beneath each slider.

-->On the Charts worksheet, replicate the formatting of the Column chart for
-->  domestic, foreign, and total sales.
-->Switch from a Column chart to a Bar chart (see where Jan, Feb, etc. appear)
-->  and then switch it back.
-->Switch the Doughnut or Radar chart (of grades) to a Column Chart (the
-->  categories are short) and include the Data Table and then switch it back.
-->These chart types all show multiple series of data nicely

A useful variant of the Column chart involves stacking each series onto one
column.

-->Remove the Total data series from the Column chart
-->Switch to Stacked Column (the second chart subtype on the top for Column).
-->The bottom color shows the size of the domestic sales; the top colors shows
-->  the size of Foreign sales.
-->The length of each bar (the total height) is the sum of the two,
-->  representing the total sales (that is, the total height of the column is
-->  just the sum of all the values in the column); this is why we removed
-->  the Total data series: it is automatically represented in the chart
-->Undo these operations to restore the chart to it original form


There is a special kind of stacked chart where the height of all data series
are the same, and the height of each series within the stack is proportional
to its percentage of the whole column. Under the 100% Stacked data, I show
how many As, Bs, Cs, Ds, and Fs four different instructors gave. I wanted to
see if we were all approximately giving the same percentage of grades, even
though we taught classes with very different numbers of students. A 100%
stacked chart allows us to easily compare the percentages of As, Bs, ... each
of us gave. Again, sometimes the right chart means the difference between
understanding and not understanding the data. 

-->Turn the "Grade Comparison Among Instructors" chart into a Doughnut chart
-->  and then a Radar chart (both charts automatically can show the data as
-->  percentages); make it as easy to read as possible; then undo the change
-->Show this data as a regular Column chart (the raw numbers shown aren't easy
-->  to interpret.


We can also add a 3-d effect by chosing the last subtype in the Column type.
The result allows us to easily see both series as its own Column chart (the
series appear in the front to back dimension) as well as compare the values for
each age group (see the Population chart). This approach doesn't work well if
there are many rows (try it with the "Grade Comparision Among Instructors"
chart).

-->Turn the Yearly Sales chart into a 3-d chart; then undo the change
-->Turn the Poplulation chart back into a regular Column chart; then undo the
-->  change


Line Charts

A Line chart uses the same series labels as a Column chart, but its Y values
are displayed as connected points instead of as columns). This chart emphasizes
how the value in one series progress (say from Jan, to Feb, ..., to Dec values
of the Domestic series), whereas the Column chart emphasize how the points in
each series compare (how the Domestic, Foreign, and Total points compare each
month).

-->Turn the Yearly Sales chart into a Line chart; then undo the change
-->Turn the Poplulation chart into a Line chart; then undo the change


Area Charts

The Area chart is like a Line chart, with the areas under each curve filled-in.
Because of this 2-d effect, often one series obscures another, no matter what
order the series appear in. A Stacked Area chart is like a stacked column
(mostly fixing this problem).

-->Turn the Sales chart into an Area chart; notice that Excel changes the 
-->  order of the data series in the legend, moving the Total to the top, so
-->  that it won't obsure the other two.
-->Undo these changes


Bubble

We can use a bubble chart to plot a 3rd value against two others. Each bubble
has an X,Y location and a size (specifying the 3rd value). For example, the
Weight Loss chart shows the amount of weight lost (proportional to the size
of the bubble) for people with specific Original Weights (X axis) and Weeks in
the Program (Y axis). By quick inspection of this chart, it looks like people
weighing about 300 lbs do best (lighter and heavier people don't do as well),
with slightly more weight lost the longer they stay in the program (but not
much if they leave very early). People not so overweight (<200 lbs) lose less
weight as do very heavy people (>350 lbs) no matter how long they stay in the
program. Of course, there aren't many data points here.

In this chart I put the "bubble size" Data Label in the chart, but then moved
the numbers to be inside the bubbles. Notice that when we Edit the Weight Loss
data series, there are 3 locations for information: X, Y, and Bubble size.

Generally, Bubble Charts work well when there are few data points, spread
far apart in the X and Y dimensions.


Surface

Another way to plot a third dimension, useful when there are many data points
in the rectangle is a Surface chart. The Heat Transfer chart illustrates a
Surface chart.

-->Right-click on the chart and click 3-D rotation
-->Experiment with Rotation on the X and Y axes, and the perspective
-->Hold down on the mouse button and move the wireframe associated with the
-->  the plot to see different views of it.

Finally, it is easy to display individual data series by selecting a different
chart type just for that series.

-->For the Yearly Sales chart, select only the Total series and change its 
-->  chart type to a Line chart


2) Trendlines:

If we make a series of measurements and plot them in Excel, we can easily use
Excel to find standard curves that approximate the data. Such curves are called
trendlines (and do a regression analysis on the data), fitting it to standard
equations with the minimum amount of error. It reduces a jumble of points to a
simple equation that runs through/near as man points as possible.

-->On the Trendline worksheet
-->Right-click on the one data series, click on Add Trendline
-->Click on various Trend/Regression types (radio buttons) to see what fits
-->  best (for polynomials, try a few increasing orders)
-->The trendline appears in the legend
-->Click on Display Equation on Chart; move that equation to a convenient spot
-->  on the chart



3) Summary:

Here is a quick summary of skills to acquire from working on this lecture.
You should be able to discuss each of these topics a bit, but more importantly
know HOW TO DO something in Excel,

Be familar with the Pie, Doughnut, Radar, Column, Line, Area, Bubble, and
Surface charts for displaying different kinds of data. Many details in these
charts as the same as XY Scatter charts.

Know how to add trendlines to charts, including positioning their defining
equation somewhere convenient.