Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
What's New
 Search Site RSS
   04/4/2008 4:30 PM


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 this site.

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's approach, from time to time, I will present a published chart, diagnose it's 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 submit.

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 March 2, 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....
Links:       Flash Video                Workbook

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.                   (Workbook link)

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.

Ravens Fan, 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

Follow this 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 - JuiceAnalytics.Com, 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 legend.

The labels are added with a simple VBA procedure included in this file. 

Request for Plotting a Line From Runway Name - WLMPilot, an Excel Chart User Forum reader, asked for help to draw runways based on the runway number (heading).

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 link 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 several features:
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  here. BP's 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 shortcomings:

  • 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 sizes.
  • Important trend information hidden in pie charts:
    • 3 countries have decreasing shares, 3 countries have increasing shares
    • China and Other EMEs have fastest rate of increase in energy share
    • 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 use

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.

Junk_Charts 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 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.  File Link  
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 relationship.

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's initial surgery.


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 if you have suggestions, thoughts or an alternative version.

Dot Plot with 2 Level Y Axis Labels Clearer than Column Chart - Stephen Few 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 version.