Updated: Spring 2020
			A Quick Tour of Other Charts


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 a chart to find one that best presents the data, although sometimes we have
to reconceptualize what data we compute to truly make its presentation clear.

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 (e.g.,
massive amounts of data) so that it can be easily understood. Of course, one
can also use "bad" graphics to present information in a way that hides (or even
misdirects us as to) 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 how to select what chart is most appropriate 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 chart it (including which order the
data series are presented in the legend). This process requires thinking and
maybe even an iterative process, experimenting with different charts to try to
find a good way to visualize the data. Try; look; improve; look; ...


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 (e.g., raw numbers or percentages) 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. The angle and area of each slice is
proportional to the value it represents.

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; to make it go counterclockwise, reverse the order of
the data). 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.

When selecting the columns to chart we include the labels at the top, Excel
will use the labels in A1&" "&B2 to title the chart and choose the labels
A3:A7 for display on the right.
 
-->Open the the workbook distributed with this lecture
-->On the Charts worksheet, replicate the exact 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 entries are the category labels
(grades), and Column B enteries are the number of students who received each
grade at Midterm, and Column C entries are the number of students who received
that grade as their Final grade. Here we can see fewer final grades of A, but
more of B such that there are almost the same number of each for midterms and
finals. All easily to with this kind of chart.

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.

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

This same kind of information is sometimes better represented as a 100% stacked
bar chart, which also allows us to easy compare multiple series: really it is
just a doughnut chart  who concentric rings have been snipped and streched.


Radar Charts

A third but similar way to view date like this is a Radar chart, which also
allows (as with doughnut charts) 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 markers). Notice
that in Radar charts we cannot automatically 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). Notice that the series values (Midterm and Final) 
appear on the right, not As, Bs, etc. but they do appear on the axes of the
radar chart (more rows would lead to more axes).

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.
I changed the lines to be only 1 pt wide (the standard widths, 2.25 pt had one
line obscuring the other) and I use small circles to as data markers.
 
-->On the Charts worksheet, replicate the formatting of the Radar chart for
-->  midterm and final grades

I have seen these kinds of charts used to compare multiple computer systems
along multiple axes (measurments).


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 Design tab on the Ribbon (under Chart tools); then click "Add
Chart Element" and hover over the Data Table option and select the format (I
chose "With Legend Keys". Sometimes we print charts on their own page, and
including all the data in text form on the chart is useful. We can also have
these values appear as data labels on or above each column (which I judged too
messy for this chart).

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 of bars. As the Gap Width decreases, each of the bars grows
wider to fill the (it can decrease to 0, in which case all the bars touch
each other) and as it increases, the bars widths' decrease, with the extra space
being put between each grouped series of bars. 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 to a Doughnut or Radar chart and then to a Bar Chart (the category
-->  names are short) and include the Data Table and then switch it back.
-->These chart types all show multiple series of data nicely
-->With a  Radar chart, it is easiest to see that Foreign sales are biggest
-->   March to July, while Domestic sales are biggest August to October

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
-->Add data labels to each series to indicate these values more clearly
-->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 choosing 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 show 2 forms: with the roles of the
-->  instructors and grades switched): which best displays the relationship in
-->  which are interested? Then undo each change
-->Show this data as a regular Column chart (the raw numbers shown aren't easy
-->  to interpret; percentages are better

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, nor if the "closer" data are larger -and thus obscure-
the data farther away (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

Sometimes it is useful to "reverse" the data displayed on the X/Y Axes to
construct a new chart. If we select a range and copy it, we can select a cell
and issued the Paste | Transpose; the range will be transposed starting at that
cell. This technique is also useful when transposing one long row into a long
column (or vice versa).

-->Delete the data in the range A110:G114 (removing data from the 2nd chart)
-->Select the range A102:E108 and copy it
-->Select cell A110 and Paste | Transpose to restore the data for the chart


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 variou Area charts.
-->Undo these changes


Bubble

We can use a bubble chart to plot a 3rd dimension/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 (and a lot more might
make the chart unreadable).

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 Format Chart Area; under the middle option
-->   disclose 3-D Rotation
-->Experiment with Rotation on the X and Y axes, and the Perspective

My notes show that in earlier versions of Excel, one could
-->Hold down on the mouse button and move the wireframe associated with the
-->  the plot to see different views of it.
But I couldn't get that to work now.


Finally, for all chart types it is easy to display individual data series by
selecting a different chart type just for that series. The result is a 
"Combo Chart"

-->For the Yearly Sales chart, select only the Total series and select
-->  Change Series 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 lines and 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 many points as possible.

-->On the Trendline worksheet
-->Right-click on the top 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: you want the lowest
-->  that fits well)
-->The trendline appears in the legend as well
-->Click on Display Equation on Chart; move that equation to a convenient spot
-->  on the chart (as with the one that is there, under the data it describes)



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 with them in Excel.

Be familiar with the Pie, Doughnut, Radar, Column, Line, Area, Bubble, and
Surface charts for displaying different kinds of data. Many details in these
charts can be manipulates as they were in the XY Scatter charts (which we
studied in detail earlier).

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

Bottom Line: The purpose of reading these notes is not just to follow the
instructions and see the results; it is to understand what you did and the
connection between what you can do and what the results will be, in general.
So, in new situations where you need to apply the skill, you'll know what to do
because you actually learned the skill while following the instructions in
these lectures and thinking about what you did.