Excel Chart Doctor
This page presents examples of published charts that have been
improved/ enhanced with the use of Cleveland/ Tufte data
visualization principles and Excel charting techniques presented in
I got the idea for this page from Kaiser Fung's
site, one of my favorite data visualization sites. Kaiser clips
interesting charts/graphs from publications and critiques them from
a data visualization standpoint, often presenting one or more
alternative ways to display the data more effectively.
Borrowing Junk_charts.com's approach, from time to time, I will
present a published chart, diagnose its data visualization health
and produce an Excel version. Readers are
encouraged to perform their own surgery on
my chart. I'll be happy to post any effective chart surgery results that readers
|Case Study: Description, Diagnosis
||Before Chart Surgery
||After Chart Surgery
Showing Categorical Data on Trend Chart - Another great New York
Times chart -
The New York Times graphics team (30 strong) is a great
source for ideas on good charting. I particularly like their
2008 trend chart of global temperature anomalies and El Nino - La
Nina episodes. This chart does not need any surgery, rather, it offers a challenge to Excel charters, how can we do that in Excel?
I've put together a workbook and video to show how we can add change
point rectangles to a XY trend chart.
Take a look....
Environmental Economics Article - Global Warming: Man versus Sun?
Environmental Economics website published this global warming article on
February 29, 2008.
The paper demonstrates effective data analysis and the use of charts to
demonstrate analysis points.
From a data visualization standpoint, the charts can be improved by
removing the Excel default background color and heavy grid lines.
This workbook included Tim Haab's data sources and recreates the charts
presented in the article, removing the chartjunk, cleaning up axis
number formats, and adding calendar values instead of month numbers.
I have posted this workbook so that users can see an example of an
effective data oriented investigation and analyze this global warming
related data on their own.
Escape from Spreadsheet Hell -
Dynamic Ranges Can Help - An Excel Chart User
forum visitor recently posted this request for help: "I am always
charting 2 years of data.
I have 2 spread sheets, one has multi charts and the other is all the
data for the charts.
I'm tracking about 6 different data points in a line chart. Each month
the data sheet gets the previous months production data. I'd like to
have it automatically adjust the charts. Drop the oldest month and add
the newest month to the chart.
That would save me the time of manually updating the charts."
Ravens Fan (Excel Chart User Forum, 12/3/07)
I invited Ravens Fan to send me his workbook. He sent it
to me. Here's my diagnosis, solution to his problem and Raven's Fans
report on how much time he saves each month using dynamic range names.
like many Excel chart users, spends way to much time editing routine
charts because he wasn't using dynamic range names to automatically
update his charts as new data was entered.
Dynamic range names are critical to avoiding spreadsheet hell and
updating routine charts.
The Ravens Fan spent 1.5 days per month updating his
charts before surgery. He reports that his time has been reduced to 10
minutes per month after surgery.
E-mail from Ravens Fan:
"Just wanted to thank you for all your
help. I finally figured out the reasoning. ... What you gave me
works great! It took my work load down from 1 1/2 days to 10 minutes.
I greatly appreciate your help" ... Ravens Fan
link to see how dynamic range names can save you time
by automatically updating your charts as new data is entered.
Replace Legend with
Series Labels -
one of my favorite sites, has an interesting tool to help users select
the appropriate chart for their data. Juice's Line Chart example,
shown on right before surgery, includes a legend that tells the reader
what color represent which data series.
Legends make it harder for the
reader to interpret the chart because he/she must move their eye back
and forth between the series and the legend.
Let's replace the legend with data labels for each series to make it
easier to interpret. We don't want our message to get lost in the
The labels are added with a simple VBA procedure included in this
Plotting a Line From Runway Name - WLMPilot, an Excel Chart User
Forum reader, asked for help to draw runways based on the runway number
This is an interesting question, because WLMPilot wants to
draw a line based on a name. The runway numbers drop the last digit of
the 3-digit heading, so 07 is actually 70 degrees..
We can use an XY chart to plot our lines. Since it only takes 2
points to define a line, we can use 0,0 to define one end and calculate
the X,Y coordinates for the 2nd point with simple trigonometry
functions. We can then plot our line with the start and end
points. This is the
to revision 2 of the workbook.
Request for Interactive Chart
- Multi Data Sheets - Joe, an Excel Charting User Forum reader,
asked if anyone had ideas on how to create an interactive chart tool
that lets the user select which data sheet and variable data column to
make the chart. The X axis is time, so Joe also wanted the tool to let
the user select the start and end time.
The resulting workbook is
available here. It provides
1.Drop down menus for the data sheet
2. Drop down menu for variable
3. Scroll bar for minimum X axis value
4. Drop Down menu for X axis time increment
5. Macro to adjust X axis for min value & increment
6. Macro to prepare list of data sheets
One Trend Chart
Better Than Two Pie Charts - BP does a great service summarizing
world energy use and making this data available
Energy in Perspective includes a graphic that compares share of
world energy use by regions for 1991 and 2006. The graphic includes two
pie charts, showing the shares for 1991 and 2006.
BP's chart shows
four factors: year, region, and share of world energy use and the
equivalent tons of oil equivalent (toe). This graphic has several
- Reader must scan back and forth between pie charts to assess
trend in regions share of energy use
- Increase in global energy use from 8.2 to 10.9 billion toe shown
directly with text notes and indirectly by adjusting pie chart
- Important trend information hidden in pie charts:
- 3 countries have decreasing shares, 3 countries have
- China and Other EMEs have fastest rate of increase in energy
- FSU has greatest rate of decrease in energy use
- US and Europe have decreasing shares of energy use
- Other OECD countries had slight increase in share of energy
The Chart Doctor's version is a simple trend chart with each region
labeled and the 1991 and 2006 data values displayed. The increasing
trend regions are noted with the black line, the decreasing share
regions noted with the gray lines.
OECD - 30 developed economies, including USA and Europe
EME - Emerging Market Economy countries
FSU - Former Soviet Union countries
Other EMEs - EMEs less China and FSU countries
Clustered Column Chart for Trends Not
Clear - Jon
Udell posted this clustered crime trend chart in an article on his
analysis of local crime trends in Keene, NH.
Jon's chart shows 3 factors in a 2 dimensional (X and Y), year,. distance
from 1 Central Square and % of annual crimes by location. He chose to
use a clustered column chart.
Clustered column charts are a poor choice for showing trends of
several series because it
is more difficult for the viewer to follow the trend pattern for a
particular location, the columns for the other series break up the
pattern, forcing the reader to work harder than necessary.
The Chart Doctor's Excel version uses a simple XY - scatter trend chart of the 3
series. It clearly shows the abrupt changes in 2006, the decrease in the <
05. mi series and the increase in the > 1 mi series. This version labels the last point so
that the legend can be removed and removes the data labels to help focus the
viewers attention on what is important, the pattern shift in 2006.
Selecting the right chart type is one of the most important steps in
making an effective chart.
Where are the crimes?
has a discussion on charting crime trends by region of the US and type
of crime. The source data is presented in the data table.
made a "profile chart" shown in the before column.
The Chart Doctor's Excel version uses a series of XY
charts with error bars to produce a panel like display so that readers
can compare regional changes by crime type.
Graphic Equity 2
JunkCharts.com has a great discussion on energy
use charting. Kaiser has shown network, mosaic, and flow diagrams
for energy source and sector use data.
Jon Peltier used this data to show how you can make a mosaic
chart in Excel.
Panel charts allow users to create
multivariate plots all in one Excel chart. My is to use a
Horizontal Panel Dot Plot.
|Operating Room Schedule - I received an e-mail
request from a real doctor who wanted help developing a chart to show
the schedule for an operating room. The doctor sent along a hand sketch.
I was happy very happy to develop the Excel chart on the right to
show the daily schedule for an operating room.
Blood Pressure Trend
- On the Excel Chart user forum, Marc asked how to put connecting lines
between data points on an XY scatter chart of blood pressure readings.
Finding it easier to make the workbook example than explain it, I
created this downloadable example. It uses Y error bars to connect the
Systolic and diastolic readings. It also uses dynamic range names to
provide automatic updating of the chart.
|Chart Should Tell the Full Story
shows an example of a junked up chart from the June, 2006 issue
of Scientific America. The problem chart tries to show the relationship
between watershed concrete/asphalt cover and tidal creek water quality.
This chart has many problems, including inappropriate chart type (bar
chart), unnecessary and distracting use of pie charts, using creek name
as X variable when real variable is % impervious cover, claiming "strong
correlation" without any information on correlation coefficient and
JunkChart solved most of the problems, except for
information on strength and type of relationship between impervious
cover and fecal coliform counts and labeling the creek names.
Here's my Excel version, building on
|Stacked Trend Bar Charts Not
Clear - Stacked bar charts are often used to compare trends of
several factors over time.
BP does a great service by preparing the annual
BP Statistical Review of World Energy and the companion
Excel workbook. BP uses stacked/clustered bar charts in their 2006
review. I will be commenting on several of them. In this case, let's
look at BP's Oil Consumption Growth for 2003 - 2005 stacked bar chart.
Since the bars are cumulative, to follow the growth trends for China and
the USA in BP chart, the reader must do some quick mental arithmetic.
My version, uses a simple XY chart so that each area has a
separate series so that the reader can see the US, China and Rest of
World values directly without and mental calculation. this version uses
a custom Y Axis and includes attaches labels to the individual series
rather than use a legend.
The Excel workbook is here
if you'd like to create your own version. Be sure to drop a
comment if you have
suggestions, thoughts or an alternative version.
Plot with 2 Level Y Axis Labels Clearer than Column Chart -
reports on two data websites that provide data charting and sharing
venues: Data360 and
Swivel. While these sites provide
access to interesting data, the data visualization quality of their
charts is limited.
Let's look at Data360's chart on
automobile CO2 emissions. There are a number of concerns:
o Vertical alignment of vehicle labels makes them difficult to read
o Vehicle type (hybrid, SUV) hidden in vehicle name
o Data labels repeat manufacturer
o Emission comparison by manufacturer, not vehicle type
o Horizontal grid not necessary
My Excel chart version uses a dot plot with a 2 level Y axis data
label so that the vehicles can be grouped by vehicle type. This version
helps to uncover hidden information that was hidden in the Data360