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


What's New

Topic Date

Using R for Advanced Charts - While trying to make advanced Excel based charts,  I often asked myself the question "...should I use R for this chart instead of  Excel". These charts were readily available in R.

I was reluctant to take on the R learning curve, I felt it was better to use a tool that I knew and avoid the learning curve of a package like R.

I finally developed a set of VBA procedures to build a full trellis chart.  When I completed the Excel - VBA trellis chart, I reconsidered R because I now know what it will take for me to develop the trellis advanced charting that was already available in R.

Why reinvent trellis charts in Excel when they were readily available in R?  To answer this question, I needed to find out how difficult the R learning curve was, I already knew what it takes to develop these tools in Excel - VBA.  The post video shows both the Excel-VBA trellis chart tool and R trellis charting.

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....
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.
VBA Code to Generate Monthly Cycle Chart Automatically -Trend charts show the progression of your data over time. Some of our time series data exhibit cyclical patterns,  possibly annual cycles related to seasons or weekly patterns related to work schedules.  This tutorial presents a workbook and VBA code that automatically produces a monthly cycle chart in Excel. 2/25/08
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 Chart Doctor diagnosis, solution to his problem and Raven's Fans report on how much time he saves each month using dynamic range names.


Downloads - I've added a downloads page to help keep track of the Excel workbook files that I have posted.  12/4/07
Segmented - Piecewise Regression - Linear regression can be done with all the data or just parts (segments) of the data to see if there are abrupt changes in the relationship. The boundaries between the segments may be called breakpoints. This post shows how to prepare a two segment regression analysis of trend data. The downloadable workbook includes an interactive method and a Solver based method to find breakpoint. 12/3/07
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  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.

Interactive Regression This post shows how to build an interactive regression worksheet that allows user to adjust X value start and end points with scroll bars, automatically compute regression values using Excel LINEST function, assess goodness of fit, plot the results, display the regression line, r2 and significance of F test on the plot. Conditional formatting is used to highlight F and t test significance. This workbook provides equivalent regression results to the Analysis Toolpak Regression procedure using standard Excel functions and formulas. 11/27/07
Request for Plotting a Line From Runway Name - An Excel Chart User Forum reader asked for help to draw runways based on the runway number (heading).

This is an interesting question, because the reader 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 the workbook.

Request for Interactive Chart - Multi Data Sheets - One of the Excel Charting User Forum readers, asked if anyone had ideas on how to create an interactive chart tool that lets the user interactively select which data sheet and variable data column to use for the Y axis values in a chart. The X axis is time, the reader also wanted the tool to let the user select the start and end time.

The resulting downloadable workbook 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

Excel Regression Tools - Excel provides a number of functions that allow users to perform regression analysis of times series/ trend data.  This post shows  6 ways to use Excel for regression analysis, from simple to advanced:

1. Add trend trend line with display of regression and r2 to chart
2. Prepare manual calculations
3. Use Excel Functions (INTERCEPT, SLOPE, RSQ)
4. Use Excel Function - LINEST
5. Use Excel's Analysis Toolpak add-in
6. Use Combination of Excel Statistical Functions 

Each approach is demonstrated using the same data set for each approach, annual average temperatures,  oC,  in Vaexjoe, Sweden between 1918 and 2001. These annual values have been calculated from daily observations archived by the European Climate Assessment and Dataset (ECAD) site. The  data and exhibits are all available in a downloadable Excel workbook.

Beginning Day of Snowmelt - 3 Midwest Basins - Earlier snowmelt is one of the potential results of global warming. This post reviews work by P Neuman and the Open Mind blog on the beginning day of snowmelt in 3 Midwest basins from 1910 to 2003. A downloadable workbook is provided. The analysis includes trend charts, moving average charts, CuSum control charts and change point analysis as well as regression analysis to understand the trends in these 3 basins. 11/3/07
One Trend Chart Better than Two Pie Charts This Chart Doctor case shows the share of world energy use by region for 1991 and 2006. The original graphic uses two pie charts to show this data.  From a data visualization standpoint, we want the reader to be able to easily compare energy share trends by region.  The two pie charts require the reader to scan back and forth between the pie charts to see the  changes for each region between 1991 and 2006. The Chart Doctor chart version uses an XY trend chart so that the reader can clearly see the energy share by region for both years and changes between these years. The Chart Doctor version  labels the1991 and 2006 point of each series to provide the actual data values. 11/1/07
Map Excel Data With Google Maps Google offers two mapping platforms: Google Earth and Google Maps. This posts shows how to access web based KML files and display them in Google Maps.  The KML file is generated by the MapExcelData.XLS workbook which incorporates VBA code by JuiceAnalytics and A Simon into a reusable tool that lets you quickly generate KML files and display your data in either Google Earth of Google Maps. 10/19/07
XY Chart Show Trends Better than Clustered Column Chart This Chart Doctor case shows trends in crime distribution by location for three categories in Keene, NH. The original version used a clustered column chart. From a data visualization standpoint, we want the reader to be able to easily compare trends by the location category. The clustered columns make it more difficult for the viewer to follow the trends of each location. The Chart Doctor chart version uses an XY trend chart so that the reader can clearly see the crime distribution trend by location. The Chart Doctor version also
labels the last point of each series rather than use a chart legend. Finally, removing the data labels
makes the chart easier to interpret.
Map Excel Data With Google Earth Google Earth is a powerful mapping tool that is readily available and can be used to import and map user data.  This workbook demonstrates how to obtain the latitude and longitude for your Excel data locations and how to export your Excel data to a Keyhole Markup Language (KML) file for easy import into Google Earth. The attached MapExcelData.XLS workbook incorporates VBA code by JuiceAnalytics and A Simon into a reusable tool that lets you quickly generate KML files and display your data in Google Earth.  10/15/07
Temperature Trend Analysis The European Climate Assessment and Dataset site maintains files of continuous daily mean temperature data for 100's of European stations. The Stockholm, Sweden station has continuous daily mean temperature data from 1756. This 250 years of daily Stockholm mean temperature data were summarized into annual mean temperatures so that I could assess the long term climate trends. The Stockholm Temperature Trend Analyzer workbook includes ten (10) trend analysis tools. 6/13/07
Interactive Tool to AnalyzeTemperature Trends: 5 Swiss Stations - Analysis and interpretation of long term temperature data can be challenging. This post shows how to make an interactive analysis tool to help in interpretation of 100 years of daily temperature data from 5 Swiss stations. 4/3/07
Multiple Trend Lines - Excel provides trend lines as part of the chart package. How can show trend lines for 2 or more subsets of your data? This example shows you how to add trend lines for up to 5 subsets of your overall data set. The example shows 4 trend lines for user selected periods of Northern Hemisphere temperature anomalies data set for the period 1880 - 2006. 3/13/07
Global Warming Trends - This page has been updated with a Law Dome ice core CO2 trend chart; a consolidated horizontal panel trend chart of Vostok, Law Dome and Mauna Loa Observatory CO2 data for the past 420,0000 years using two time scales in the same chart CO2 emission trends chart since 1750, a horizontal panel chart of CO2 emissions per capita for selected countries in 2000 and a link to an interesting YouTube Video on an innovative way to display a global temperature trend chart. 2/24/07
Paleoclimate Trends - Continuous temperature records based on thermometers only go back about 100 years. Paleoclimatology, the study of past climate, uses several proxy data techniques to estimate climate conditions over geologic time scales. The collaborative ice-core project between Russia, the United States, and France at the Vostok station in Antarctica extend the climate record back approximately 420,0000 years in time. This page includes an Excel based vertical panel chart of the CO2, methane and temperature trends for the past 420,000 years. 2/5/07
Global Temperature Changes Temperature changes by location, day of year and time of day. How do we assess long term climate changes? To facilitate assessments of long term climate trends, climatologists compare the global mean temperature for a base period with the annual mean for individual years. Differences between the annual mean and baseline mean are called anomalies.  NASA's Goddard Institute of Space Studies (GISS) uses the 1951 - 1980 period for their baseline period. They use the difference between the global temperature mean for a year and the baseline mean to determine the global temperature anomaly for the year.

This page presents an Excel based chart, using GISS data available, that shows the annual global temperature anomalies for the period 1880 - 2006.

Custom Chart Axis Tutorial  XY Charts let us accurately place data markers exactly where we want in a 2 dimensional grid. Excel lets us format our chart axes by adjusting the scale range, interval and axis number formats.

There are times when we need to use more than Excel's standard XY Chart axis formatting  to have the X or Y axis look the way we want. We can use custom axes to make those critical charts that Microsoft did not include with Excel: dot plots, bumps charts, panel charts and others.

Dot Plot with 2 Level Y Axis Labels This Chart Doctor case shows a column chart of automobile CO2 emissions by vehicle. The vehicle labels are aligned vertically, making reading difficult. The vehicle name combines manufacturer, vehicle type and model into a single name, limiting group comparisons. From a data visualization standpoint, we want the reader to be able to easily compare emissions by vehicle type, not have to scan across. The Excel chart version uses a dot plot with 2 level Y axis labels so that the reader can compare by vehicle type.
Stacked Bar Charts Not Clear - This Chart Doctor case shows a stacked bar chart of world oil consumption growth and an alternative trend chart. Stacked bar charts are a poor chart type in many cases because they require the reader to do quick arithmetic calculations to assess the lengths of the bar segments to assess the changes. From a data visualization standpoint, we want the reader to see the trend, not have to calculate it. The Excel workbook includes the data and shows how to add custom X and Y axes.  12/1/06
Banking to 45o to Enhance Visualization Cleveland has shown that we can enhance XY chart visualization by careful selection of the data rectangle (area inside axes) height to width (h/w) aspect ratio. Banking to 45o is a technique that adjusts the aspect ratio so that the line segment orientations average 45o. This page presents an Excel workbook that implements Cleveland's aspect ratio and banking to 45o techniques. 11/15/06
Advanced Charting Techniques - I use a number of techniques and tips over and over to make my enhanced Excel charts. I have grouped these techniques into 5 learning steps to provide my recommended road-map for learning enhanced Excel charting. These techniques will help you to add lines, add text, make dummy axis series, label points, make dynamic/interactive charts and dashboards. 10/30/06
Chart Doctor - 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 his  hand sketch. He wanted to be able to show time of operation from 6:00 AM till 8:00 PM .
I developed a floating bar chart using a simple XY chart with error bars. 

Sea Level Changes - The United Nations Environment Programme reports that mean sea levels have risen 10 to 25 cm over the past 100 years. This sea level increase is caused by thermal expansion of warmer water, retreat of glaciers and ice caps and a net positive contribution from the huge ice sheets of Greenland and Antarctica.

The linked workbook provides sea level trend data for 11 locations along the North Atlantic. Users can select the location from a pull down list and generate a trend chart for that location.

CO2 Trends - CO2 has been increasing as a result of fossil fuels. This  page presents an Excel based CO2 trend chart and monthly cycle chart of monthly CO2 data from  Mauna Loa Observatory, Hawaii. 10/14/06
Global Warming Trends - Global warming is a very serious, complex and controversial subject. This page presents Excel charts of global warming related data.  Each post includes a link to the data source and where appropriate, sites that provide the scientific/theoretical underpinnings to the demonstrated analysis. 10/14/06
Global Environmental Trends - As an environmental engineer, I have been concerned about the environment for many years.  This new page,  combing my data analysis and environmental  interests,  presents Excel charts that show global environmental trends.

I have made each chart in Excel from on-line data sources.  The workbooks are available on request. 

Chart Doctor - This new 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.

Data Visualization and Excel - In deciding what type of chart/ graph to use, it is helpful to understand your chart viewers graphical feature interpretation skills.  W. Cleveland (1984) conducted experiments to measure these abilities. This page uses Cleveland's results to assess standard Excel charts effectiveness. 6/20/06
Panel Charts W. Cleveland advocates the use of trellis like displays, Tufte advocates the use of small multiple charts. Advanced statistical and graphical packages allow users to prepare trellis, small multiple charts.
While not a default chart type, Excel users can develop trellis display/small-multiples like charts in Excel by using standard
Bumps Chart - These charts are very useful in showing before - after changes in rank or value. Originally developed to track crew race outcomes in England, they can be applied to many situations where excel users rely on stacked or clustered bar charts. This page shows several Excel bumps charts. 6/20/06
Panel Dot Plot - While Excel provides both pie and doughnut charts, there are a number of data visualization problems associated with these charts so that users should rarely use them. Dot plots provide Excel chart users an effective alternative to pie/ doughnut charts.  Panel dot plots can be used to show changes over time with small multiple -  trellis like displays. This page  and download shows an Excel panel dot plot using standard Excel charting, data transformations and dummy axis techniques that are all explained in this site. 6/7/06
Horizontal Panel Chart - Alternative to Stacked Column Chart - Stacked charts have data visualization limitations such that users should look for alternatives before using them. Horizontal panel charts are a good alternative. this example shows how to use dummy axes to construct a panel chart similar to S-plus trellis like charts.

Shows horizontal panel chart of UK Hospital Bed trends for the period FY 1988 to 2005. The original stacked column chart is shown to provide a comparison of the effectiveness of both chart types.  

Parallel  XY Trend Chart in Excel Data visualization researchers have similar advice when plotting multivariate data: Tufte and Few recommend "small multiples", Cleveland and Robbins  recommend "Trellis Displays". Advanced statistical and graphical analysis systems like R and S-Plus include Trellis display capabilities. Trellis like chart displays increase your ability to visualize your data. This tutorial and example workbook show you how to build a small multiples trend chart to get a trellis like display affect in Excel. 5/24/06
Monthly Cycle Chart in Excel Trellis like chart displays increase your ability to visualize your. This tutorial and example workbook show you how to build a monthly cycle chart to get a trellis like display affect in Excel. 5/22/06
No Chart Junk New page dedicated to data visualization information for the rest of us. Includes reviews on data visualization books, links to data visualization sites, and links to articles on pros and cons of Excel for statistical analysis and charting. 5/16/06
Date Dummy Axis Series We can use a dummy date axis series to label our months and/or years the way we want, and improve on the default data formatting. The workbook also shows how to build a Step chart in Excel. 5/9/06
Stacked Trend Charts - Data Transformation Technique - This tutorial is one of two on this Site that show you how to build a series of XY trend charts that are aligned vertically so that you can compare trends between several parameters at the same time. This page shows the data transformation and dummy Y axis series technique and is based on the example found in 5/2/06
Learn How To Add Events to Your Trend Charts - Trend charts show values for a data series over time. There are usually outside factors that affect our trend data series. The addition of these events can significantly enhance the educational value of our trend charts. This tutorial and workbook show you how to add event information to your trend chart to increases its explanatory value. 5/1/06
Interactive - Dynamic Excel Trend Chart video  - Excel has powerful interactive charting capabilities that many Users do not use because they are unaware of them. This video and workbook demonstrate how a combination of dynamic range names, controls and VBA can be used to transform static data and charts to an interactive tool that can help users analyze and understand their trend data.                  Workbook Download 4/28/06
Dot Plots - Alternative to Pie Charts - Many data visualization writers recommend against use of Pie charts. This  workbook compares a Dot plot and Pie Chart for the same data. It shows you how to create axis label (dummy) series for Y axis and log scale X axis. 4/13/06
Interactive Trend Chart - Checkboxes - This workbook shows you how to use dynamic range names and checkboxes to let user select which data series should be plotted. 4/13/06
Excel Box Plots - 2 Working Examples  - Excel does not provide a pre-made Box Plot option. With a few steps, however, Users can make there own Box Plots. 4/12/06
Excel Chart Links -This workbook includes links to 150 Excel chart articles and downloads. Use the built in query macro to find links to articles on your topic. 4/7/06
Excel Dashboard Sheet Layout Video - This 5 minute video shows you how to layout your worksheet. The accompanying workbook includes the  VBA procedure demonstrated in the Video, 4/6/06
Data Scrubbing - This workbook  includes 7 VBA procedures to handle a wide range of data scrubbing situations often found in working with process data. 4/5/06
Using Excel's Camera Tool with Dashboards 3/31/06
Process Dashboard Video 3/30/06
Day of Year Trend Chart 3/28/06
XY Trend Chart Animation 3/24/06
Excel Process Data How To's 3/23/06
Process Data Tool Kit Video - 6 minute Introduction to this Excel -VBA treatment plant data management system 3/21/06
Trend Charts and Missing Data 3/17/06
Size - Align Charts 3/15/06
Freeze Text Size 3/15/06
Interactive Moving Average Chart 3/14/06
Add Vertical Line to XY Chart 3/13/06
Highlight Min & Max Values on XY Chart 3/13/06
Adjusting Cell Size with VBA 3/10/06
Making a Chart in a Cell with VBA 3/10/06
Build Control Chart 3/10/06
 Add Horizontal Line  to XY Chart 3/10/06
Stacked XY Trend Charts   3/10/06
Control  X Axis Min - Max Values    3/10/06

04/04/2008 03:15 PM