ProcessTrends.Com
Home
Charts - Graphs
Climate Trends
Global Warming
R Resources
Excel Resources
Google Maps
What's New
  About       Contact
 Search Site RSS

   2/18/2010

 

Data Visualization & Excel Charts

"Like good writing, good graphical displays of data communicate ideas with clarity, precision, and efficiency.
Like poor writing, bad graphical displays distort or obscure the data, make it harder to understand or compare, or otherwise thwart the communicative effect which the graph should convey.
"
Michael Friendly -  Gallery of Data Visualization

Data Visualization is the graphical presentation of  multidimensional data so that viewers can understand the underlying structure and relationships hidden in the data. Cleveland, Few, Robbins, TufteWilkinson are some of the leading investigators in this field.

Many Excel Users rely on Excel's default charts types and settings to produce their charts and graphs without regard for data visualization principles. Default Excel charts are not effective data visualization tools. By combining data visualization principles and advanced Excel charting techniques, Excel charts can be made into powerful data visualization tools.

Theses page shows how to clean up Excel chart defaults, apply graphical interpretation skills, select the right chart for your data, size your chart based so that the key data message comes through.

 

Cleaning Up Excel's Chart Defaults

People's Graphical Feature Interpretation Skills

Aspect Ratio -
Banking to 45o
Charting Multivariate Data What's Wrong With Clustered Column Charts?

What's Wrong With Pie Charts?

 

People's Graphical Feature Interpretation Skills

To decide on the best chart/ graph to use, it is important to understand  your chart viewers graphical interpretation skills. Cleveland (1984) conducted experiments to measure these abilities.   He found accuracy skills  rank as follows (Robbins):

  1. Position along a common scale

  2. Position along identical, non aligned scales

  3. Length  

  4. Angle-slope     

  5. Area

  6. Volume

  7. Color hue - color saturation - density

How Does This Help Me with My Charting Work?

Cleveland's research shows that the choice of chart/graph affects the charts viewers ability to interpret the information. With a pie chart (criticized in several places in this site), we use angles to compare the data, a relatively poor interpretation skill for chart viewers. For stacked bar charts, we rely on bar lengths for comparisons of internal bar segments that do not a have a common axis, not as effective as position along nonaligned of common scales.

By understanding your viewers' skills, you will have a better chance selecting the chart format that they will be able to effectively interpret.

Data Visualization Perspective - What's Wrong With Pie Charts?

The pie chart is a good example of how  Cleveland's research fits into data visualization . Many data visualization writers like Edward Tufte, Stephen Few, Naomi Robbins and Howard Wainer do not use Pie Charts. The US Energy Information Administration (EIA's) Guidelines for Statistical Graphs, a useful resource on statistical charting, shares some thoughts on pie charts.  Selected excerpts:

  • Edward Tufte, in The Visual Display of Quantitative Data, wrote "the only worse design than a pie chart is several of them."

  • Howard Wainer of the Educational Testing Service stated in a 1987 Independent Expert Review of EIA Statistical Graphs policies that "the use of pie charts is almost never justified" and that they "ought not to be used." Wainer recommended to EIA that dot charts be used instead of pie charts in EIA products.

  • William Eddy of Carnegie-Mellon University, formerly vice chair of the American Statistical Association (ASA) Committee on Energy Statistics, said of pie charts at the April 1988 ASA committee meetings in a session on the EIA Standards Manual, "death to pie charts."

Cleveland's graphic interpretation research helps to explain the poor quality of pie charts as a communication device.

Dot plots are an excellent alternative to pie charts because they show data position along a common scale rather than rely on pie chart angles.

Charting Multivariate Data

The number of variables that we are working with affects the types of charts that we need to use. Most data charting situations can be grouped into 3 conditions:

  • Single variable (univariate)
  • Two variables (bivariate) 
  • Three or more variables (multivariate)

Excel's univariate and bivariate charting capabilities are effective as long as the User avoids chartjunk. Histograms, box plots, dot plots can effectively summarize univariate data. Simple bar/column, line/XY scatterplots can effectively summarize bivariate data.

Excel users pre-made multivariate charting options, however,  are limited:

 

Excel's Built-In Multivariate Chart Options

Line Chart - Multiple Series Effective when series have comparable Y axis range. Many researchers challenge use of 2nd Y axis
XY Scatterplot - Multiple Series Effective when series have comparable Y axis range. Many researchers challenge use of 2nd Y axis
Stacked Area Chart  It is very difficult to judge size of 2nd, 3rd data series because they do not have common baseline.
Clustered Column Chart Difficult to follow trend  if more than 2-3 groups.
Stacked Column Chart It is very difficult to judge size of 2nd, 3rd data series because they do not have common baseline.
Clustered Bar Chart Difficult to follow trend  if more than 2-3 groups
Stacked Bar Chart It is very difficult to judge size of 2nd, 3rd data series because they do not have common baseline.
Bubble Chart Viewers do not interpret changes in area very accurately, making it difficult to interpret bubble charts.

Excel panel charts, similar to  Cleveland's trellis display and Tufte's small multiples, present multivariate data in a form that can more easily interpreted than Excel's stacked/ clustered bar/ column  charts.